• Arun Kumar

Tablespace Administration

Administering Tablespace is one of the most important jobs of an Oracle DBA. It makes sure that database has enough space to accommodate new data and application can function smoothly without any space issues


Find Tablespace & Associated Data Files


Query V$TABLESPACE or DBA_TABLESPACES to find tablespaces inside a database

SQL> select name from v$tablespace;
SQL> select tablespace_name from dba_tablespaces;

To find datafiles associated with tablespace

SQL> select tablespace_name, file_name, bytes/1024/1024 
from dba_data_files where tablespace_name='&tablespace_name';

To find temp files associated with a temp tablespace

SQL> select file_name, bytes/1024/1024from dba_temp_files;


Find Tablespace Utilization


Use below query to find % used inside a tablespace

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';


Create Tablespace


To create new tablespace inside database

SQL> Create tablespace test_tbs datafile '/u01/test_tbs_01.dbf' size 50m;

Where:

  • test_tbs is the name of new tablespace

  • /u01/test_tbs_01.dbf is the location of the datafile

  • 50m is the size of the datafile



Add Space to Tablespace


There are two ways to add space to a tablespace:

  • Resize existing datafile

  • Add new datafile

Before adding space, you must check OS level space. Never proceed without checking it

Use below command to resize a datafile

SQL> Alter database datafile '/u01/test_tbs_01.dbf' resize 100m;

Use below command to add new datafile to tablespace

SQL> Alter tablespace test_tbs add datafile '/u01/test_tbs_02.dbf' size 50m;


Drop Tablespace


Below command will drop tablespace including all its contents and associated datafiles

SQL> drop tablespace test_tbs including contents and datafiles;


Tablespace Coalesce


  • Tablespace Coalesce combines all contiguous free extents into larger contiguous extents inside all datafiles

  • It takes any free extents that are right next to some other free extent and make one bigger free extent

  • SMON will perform this coalescing in the background but if you need it to happen right now, coalesce will do it

SQL> ALTER TABLESPACE USERS COALESCE;

591 views

Recent Posts

See All

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space i