Tablespace - free space.sql

The SQL*Plus script below will display the allocated space, free space and percentage of free space for each tablespace.

SET LINESIZE 85
SET PAGESIZE 200
column tablespace_name format a18
column file_name format a25
column Allocated_kb format 999,999,999
column free_kb format 999,999,999
column Percent_Free format 999

SELECT
   df.tablespace_name,
   df.file_name,
   df.bytes/1024 Allocated_kb,
   free.free_kb,
   Round(free.free_kb/(df.bytes/1024)*100) Percent_Free
FROM
   dba_data_files df,
   (SELECT file_id, SUM(bytes)/1024 free_kb
    FROM dba_free_space GROUP BY file_id) free
WHERE
   df.file_id=free.file_id
ORDER BY
   Percent_Free;

--It is normal for the UNDO tablespace to appear full or nearly full.

Note that if the tablespace has AutoExtend=ON then free space will be determined by the disc size rather than the tablespace size.

“It is the final proof of God's omnipotence that he need not exist in order to save us” - Peter De Vries (The Mackerel Plaza)

Related Oracle Commands:

DBA_DATA_FILES
DBA_FREE_SPACE