ADVERTISEMENT

Oracle Tablespace Management

In Oracle, a tablespace is a logical storage unit — it groups physical datafiles where your actual data lives. Knowing the different types helps you manage space, performance, and organization more effectively.

📌 Oracle Tablespace Types – Quick Reference

  • SYSTEM: Core database metadata — data dictionary, system catalogs.
  • SYSAUX: Supports SYSTEM — stores metadata for OEM, AWR, etc.
  • UNDO: Tracks changes for rollback and recovery.
  • TEMP: Holds temporary data (e.g., sorts, hash joins).
  • DEFAULT: Where user objects go by default unless specified.
  • BIGFILE: Single large datafile — easier to manage in big DBs.
  • SMALLFILE: Default type — uses multiple smaller datafiles.
  • USER: Custom tablespace for app/user data — keeps things organized.

💡 Use these scripts to monitor & manage tablespaces efficiently — essential for daily DBA tasks.

Check All Tablespaces

set line 100
set pagesize 100
col TABLESPACE format a30;
col TOTAL_MB format 999,999,999
col USED_MB format 999,999,999
col FREE_MB format 999,999,999
col %_USED format 999.99
break on report 
compute sum of TOTAL_MB on report
compute sum of USED_MB on report
compute sum of FREE_MB on report
select          a.tablespace_name "TABLESPACE",
		(b.bytes/1048576) "TOTAL_MB",
		nvl((c.bytes/1048576),0) "USED_MB",
		nvl((d.bytes/1048576),0) "FREE_MB",
		nvl((c.bytes/b.bytes)*100,0) "%_USED"
from            (select tablespace_name from dba_tablespaces) a,
		(select tablespace_name,bytes from sys.sm$ts_avail) b,
		(select tablespace_name,bytes from sys.sm$ts_USED) c,
		(select tablespace_name,bytes from sys.sm$ts_FREE) d
where           a.tablespace_name= b.tablespace_name(+)
and             a.tablespace_name= c.tablespace_name(+)
and             a.tablespace_name= d.tablespace_name(+)
order by 5 desc;

Check a Specific Tablespace

set lines 100
set pages 1000
col tspace format a30
colu tot_ts_size format 99999.999
colu free_ts_size format 99999.999
colu used_ts_size format 99999.999
select df.tablespace_name tspace,
df.bytes/(1024*1024*1024) tot_ts_size_GB,
(df.bytes/(1024*1024*1024) -sum(fs.bytes)/(1024*1024*1024)) Used_ts_size_GB,
sum(fs.bytes)/(1024*1024*1024) free_ts_size_GB,
round(sum(fs.bytes)*100/df.bytes) free_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) used_pct1
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files  group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name and fs.tablespace_name='&name'
group by df.tablespace_name, df.bytes
order by 6 desc
/

Check Tablespaces with Autoextension Enabled

set linesize 100
set pagesize 100
select
a.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)/(1024*1024*1024)))) MAX_GB,
round(SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024),2) USED_GB,
round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) -round(c.Free/1024/1024/1024))),2) FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024) -round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)/(1024*1024*1024)))),2) USED_PCT
from
dba_data_files a,
sys.filext$ b,
(SELECT
d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM
dba_tablespaces d,
DBA_FREE_SPACE c
WHERE
d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE
a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name and a.tablespace_name='&name'
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;

Top 20 Segments for a Given Tablespace

set lines 200 pages 45
col OWNER for a30
col PARTITION_NAME for a20
col SEGMENT_NAME for a30
col SEGMENT_TYPE for a30
select * from (
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 size_in_gb ,TABLESPACE_NAME,RELATIVE_FNO 
from dba_segments
where TABLESPACE_NAME like '%&TS_name%'
Order by size_in_gb desc 
) where rownum < 21;

Check High Water Mark

-- Set display parameters
SET lines 200 pages 100
COL file_name FOR a80

-- Query to calculate smallest possible size (HWM), current size, and possible space savings per datafile
SELECT 
    file_name,
    CEIL((NVL(hwm, 1) * (SELECT block_size FROM v$datafile WHERE file# = a.file_id)) / 1024 / 1024) AS smallest_MB,
    CEIL(blocks * (SELECT block_size FROM v$datafile WHERE file# = a.file_id) / 1024 / 1024) AS currsize_MB,
    CEIL(blocks * (SELECT block_size FROM v$datafile WHERE file# = a.file_id) / 1024 / 1024) 
      - CEIL((NVL(hwm, 1) * (SELECT block_size FROM v$datafile WHERE file# = a.file_id)) / 1024 / 1024) AS savings_MB
FROM 
    dba_data_files a,
    (
        SELECT 
            file_id,
            MAX(block_id + blocks - 1) AS hwm
        FROM 
            dba_extents
        GROUP BY 
            file_id
    ) b
WHERE 
    a.file_id = b.file_id(+)
    AND a.file_name LIKE '%/path/to/your/tablespace/data%'
ORDER BY 
    4 DESC;

Note: You need to change the mount point path in the above query as needed.

Check UNDO Tablespace Usage

select df.tablespace_name "Tablespace", 
totalusedspace "Used MB", 
(df.totalspace - tu.totalusedspace) "Free MB", 
df.totalspace "Total MB", 
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" 
from 
(select tablespace_name, 
SUM (DECODE (Autoextensible,'YES', maxbytes/ 1048576,'NO', BYTES/ 1048576)) TotalSpace 
from dba_data_files 
group by tablespace_name) df, 
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name 
from dba_segments 
group by tablespace_name) tu 
where df.tablespace_name = tu.tablespace_name and df.tablespace_name='&Tab' order by 5;

Check Datafile Details for a Tablespace

Set lines 400
col file_name for a65
select FILE_NAME, TABLESPACE_NAME,BYTES/1024/1024, MAXBYTES/1024/1024 ,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='&TABLESPACE_NAME';

Verify Datafile Creation Time

SET LINESIZE 200
SET PAGESIZE 50
SET COLSEP '    '
COLUMN creation_time FORMAT A20 HEADING 'Creation Time'
COLUMN name FORMAT A50 HEADING 'Datafile Name'

SELECT TO_CHAR(CREATION_TIME, 'DD-MM-YYYY HH24:MI:SS') AS creation_time, 
       name 
FROM v$datafile 
WHERE name = 'C:\ORACLE19C\ORADATA\ORCL\SYSTEM01.DBF';

Add Datafiles

-- Add Datafile to W3BUDDY_TEST Tablespace (Windows)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE 'C:\ORCL\ORADATA\W3BUDDY\W3BUDDY_TEST01.DBF' SIZE 1G;

-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Windows)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE SIZE 1024M AUTOEXTEND ON MAXSIZE 32765M;

-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Windows)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE 'D:\ORCL\ORADATA\W3BUDDY\DBF\W3BUDDY_TEST_45.DBF' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE 31G;

-- Add Datafile to W3BUDDY_TEST Tablespace (Linux)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '/var/opt/oracle/oradata/W3BUDDY/W3BUDDY_TEST_2.DBF' SIZE 31G;

-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Linux)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_03.DBF' SIZE 1G AUTOEXTEND ON NEXT 64M MAXSIZE 20G;

-- Add Tempfile to W3BUDDY_TEST Tablespace (Linux)
ALTER TABLESPACE W3BUDDY_TEST ADD TEMPFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_TEMP_5.DBF' SIZE 20G;

Resize Datafiles

-- Resize the datafile for W3BUDDY_TEST_DATAFILE_1.dbf in W3BUDDY_TEST tablespace
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_DATAFILE_1.dbf' RESIZE 31G;

-- Resize the datafile for W3BUDDY_TEST_DATAFILE_2.dbf in W3BUDDY_TEST tablespace
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_DATAFILE_2.dbf' SIZE 30G;

-- Resize the datafile for W3BUDDY_TEST_UNDOTBS.dbf in W3BUDDY_TEST tablespace
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_UNDOTBS.dbf' AUTOEXTEND ON MAXSIZE 25G;

-- Resize the datafile for W3BUDDY_TEST_DATAFILE_3.dbf in W3BUDDY_TEST tablespace
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_DATAFILE_3.dbf' AUTOEXTEND ON MAXSIZE 15G;

Enable/Disable Autoextend for a Datafile

-- Alter database datafile to enable autoextend
ALTER DATABASE DATAFILE '/u01/data/datafile_1.dbf' AUTOEXTEND ON;

-- Alter database datafile to disable autoextend
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_2.dbf' AUTOEXTEND OFF;

Take a Datafile Offline/Online

Alter database datafile '/u01/data/users02.dbf' offline; 
Alter database datafile '/u01/data/users02.dbf' online;

Drop a Datafile

Alter tablespace USERS drop datafile '/u01/data/users02.dbf';

Rename or Move a Datafile

-- Alter database to move datafile to a new location
ALTER DATABASE MOVE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf' TO '/w3buddy/oracle/oradata/W3BUDDY/datafile_2.dbf';

Create a New Tablespace

-- Create tablespace W3BUDDY_TEST with a datafile (5GB size with autoextend)
CREATE TABLESPACE W3BUDDY_TEST DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M;

-- Create tablespace W3BUDDY_TEST2 with a datafile (1GB size with autoextend, max size 5GB)
CREATE TABLESPACE W3BUDDY_TEST2 DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_2.dbf' SIZE 1G AUTOEXTEND ON NEXT 64M MAXSIZE 5G;

Create a Bigfile Tablespace

-- Create BIGFILE tablespace W3BUDDY_BIGTS with a datafile (100GB size with autoextend)
CREATE BIGFILE TABLESPACE W3BUDDY_BIGTS DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_bigts.dbf' SIZE 100G AUTOEXTEND ON NEXT 1G;

Create a Tablespace on ASM Diskgroup

Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;

Rename a Tablespace in Oracle Database

-- Query to check the file details for FILE_ID 37
SELECT file_id, file_name, tablespace_name 
FROM dba_data_files 
WHERE file_id = 37;

-- Output:
-- FILE_ID    FILE_NAME                                          TABLESPACE_NAME
-- -------    -------------------------------------------------  ------------------------------
-- 37         /w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf      W3BUDDY_TEST

-- Rename the tablespace_name from W3BUDDY_TEST to W3BUDDY_PRODUCING
ALTER TABLESPACE W3BUDDY_TEST RENAME TO W3BUDDY_PRODUCING;

-- Query to verify the updated details for FILE_ID 37
SELECT file_id, file_name, tablespace_name 
FROM dba_data_files 
WHERE file_id = 37;

-- Output:
-- FILE_ID    FILE_NAME                                           TABLESPACE_NAME
-- ---------- --------------------------------------------------- ------------------------------
-- 37         /w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf       W3BUDDY_PRODUCING

Drop Tablespace

-- Drop the tablespace W3BUDDY_TEST
DROP TABLESPACE W3BUDDY_TEST;

-- Query to check file names associated with the tablespace W3BUDDY_TEST
SELECT file_name 
FROM dba_data_files 
WHERE tablespace_name = 'W3BUDDY_TEST';

-- Drop the tablespace W3BUDDY_TEST including the physical datafiles
DROP TABLESPACE W3BUDDY_TEST INCLUDING CONTENTS AND DATAFILES;

-- Take the tablespace W3BUDDY_TEST_DATA offline
ALTER TABLESPACE W3BUDDY_TEST_DATA OFFLINE;

-- Drop the tablespace W3BUDDY_TEST_DATA including contents and datafiles
DROP TABLESPACE W3BUDDY_TEST_DATA INCLUDING CONTENTS AND DATAFILES;

Tablespace Growth

set lines 400 pages 200
alter session set nls_date_format='yyyy-mm-dd';
with t as (
select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
from
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot 
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and   su.tablespace_id = ts.ts#
and   ts.name          = dt.tablespace_name 
and   ts.name          =upper('USERS'))
select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb,
case when e.used_size_gb > b.used_size_gb then to_char(e.used_size_gb - b.used_size_gb)
     when e.used_size_gb = b.used_size_gb then '***NO DATA GROWTH'
     when e.used_size_gb < b.used_size_gb then '******DATA PURGED' end variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1;

Datafile Shrink Analysis and Resizing Script

set lines 1000 
SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size", 
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.", 
   ceil( blocks*(a.BlockSize)/1024/1024) - 
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings", 
   'alter database datafile '''|| file_name || ''' resize ' || 
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100 || 'm;' "Command" 
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a 
JOIN v$parameter p ON p.Name='db_block_size') a 
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b 
ON a.file_id = b.file_id 
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) 
   > 100 /* Minimum MB it must shrink by to be considered. */ 
ORDER BY "Savings" Desc;

This script helps identify and reclaim unused space from Oracle datafiles by calculating the current size, smallest possible size (based on high watermark), and the reclaimable savings. It also generates a safe ALTER DATABASE DATAFILE ... RESIZE command.

What it shows:

  • Current Size – Actual size of the datafile in MB.
  • Smallest Poss. – Minimum safe size based on data usage (HWM).
  • Savings – Space you can reclaim.
  • Command – SQL to shrink the file (rounded to nearest 100MB).

How it works:

  • Pulls block size from V$PARAMETER.
  • Uses DBA_DATA_FILES for file details.
  • Joins with DBA_EXTENTS to get the HWM.
  • Filters files with >100MB unused space.
  • Outputs sorted list by potential savings.

Safety tips:

  • Run on non-prod first.
  • Files won’t shrink below the HWM.
  • Make sure shrinking is supported by your storage/filesystem.

ADVERTISEMENT