Tablespaces and Datafiles

To view tablespace information:

set colsep |

set linesize 100 pages 100 trimspool on numwidth 14 

col name format a25

col owner format a15 

col "Used (GB)" format a15

col "Free (GB)" format a15 

col "(Used) %" format a15 

col "Size (M)" format a15 

SELECT d.status "Status", d.tablespace_name "Name", 

 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 

 TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", 

 TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 

 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"

 FROM sys.dba_tablespaces d, 

 (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 

 (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 

 d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 

 (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 

UNION ALL 

SELECT d.status 

 "Status", d.tablespace_name "Name", 

 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 

 TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",

 TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 

 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" 

 FROM sys.dba_tablespaces d, 

 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 

 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 

 d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'; 

Creating Tablespace

create tablespace tbs1
datafile '/u01/prod/tbs01.dbf'
size 10m
autoextend on
maxsize 100m
default storage (next 10m);

create tablespace tbs1
datafile '/u01/prod/tbs01.dbf'
size 10m
autoextend on;

To check tablespace information

select tablespace_name from dba_tablespaces;

select tablespace_name, contents, status from dba_tablespaces order by 2;

select name from v$tablespace;

select tablespace_name, file_name from dba_data_files;


Dropping a tablespace

drop tablespace tbs1;
(this will drop a tablespace logically, physical datafile still exists).

drop tablespace tbs1 including contents and datafiles;
(this will delete both tablespace and datafile)

Adding a datafile to a tablespace

column tablespace_name format a20
column file_name format a30
select tablespace_name, file_name from dba_data_files order by 1;

alter tablespace tbs1
add datafile '/u01/prod/tbs02.dbf'
size 10m
autoextend on;

Deleting a datafile from a tablespace

alter tablespace tbs1
drop datafile '/u01/prod/tbs02.dbf';

alter tablespace tbs1 drop datafile 7;

select tablespace_name, file_id, file_name from dba_data_files order by 2;

Resizing a datafile

alter database 

datafile '/u01/prod/tbs02.dbf' 

resize 50m;

alter database datafile 4 resize 50m;

Reusing an Orphan datafile

create tablespace tbs2
datafile '/u01/prod/tbs02.dbf'
reuse;

Making tablespace read only

alter tablespace tbs1 read only;

alter tablespace tbs1 read write;

Making tablespace offline

alter tablespace tbs1 offline;

alter tablespace tbs1 online;

Renaming a tablespace

alter tablespace tbs1 rename to tbs2;

Renaming a datafile in a tablespace

alter tablespace tbs1 offline;

$cd /u01/prod

mv tbs01.dbf tbs02.dbf

alter database 

rename file '/u01/prod/tbs01.dbf' to '/u01/prod/tbs02.dbf';

alter tablespace tbs1 online;

select tablespace_name, file_name from dba_data_files;

Relocating a datafile in a tablespace

alter tablespace tbs1 offline;

$mv /u01/prod/tbs01.dbf /u02/prod1/tbs01.dbf

alter database 

rename file '/u01/prod/tbs01.dbf' to '/u02/prod1/tbs01.dbf';

alter tablespace tbs1 online;

select tablespace_name, file_name from dba_data_files;

Moving a table from one tablespace to another tablespace

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>;

alter table emp move tablespace tbs1;

Below command creates a table T1 in our selected tablespace:

CREATE TABLE T1 TABLESPACE TBS as SELECT * FROM SCOTT.EMP;

Moving index from one tablespace to another tablespace

alter index emp_indx rebuild tablespace tbs1;

To check database size

select sum(bytes)/1024/1024 "Size in MB" from dba_data_files;

To check free space in database

select sum(bytes)/1024/1024 "free space" from dba_free_space;

Creating the locally managed tablespace (LMT) with uniform extent size

create tablespace tbs1

datafile '/u01/prod/tbs01.dbf' 

size 20m

EXTENT MANAGEMENT LOCAL 

UNIFORM SIZE 100k;

Tablespace with ASSM (Automatic Segment Space Management)

create tablespace tbs1

datafile '/u01/prod/tbs01.dbf' 

size 20m

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100k

SEGMENT SPACE MANAGEMENT auto;

select tablespace_name, segment_space_management from dba_tablespaces;

Creating bigfile tablespace

create bigfile tablespace bigtbs

datafile '/u01/prod/bigtbs01.dbf'

size 3g;

select tablespace_name, bigfile from dba_tablespaces;

select tablespace_name, bigfile from dba_tablespaces where bigfile='YES';