Script to extract size of Oracle tablespaces & associated datafiles

By issuing below script you will extract the total size of tablespces along with Total space available in MB, Free space available in MB & Free percentage(%)

COLUMN tablespace_name FORMAT A17
SELECT t.tablespace_name, t.total_space_in_MB, f.free_space_in_MB,

FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 Total_space_in_MB FROM DBA_DATA_FILES GROUP BY tablespace_name) t,
(SELECT tablespace_name, SUM(bytes)/1024/1024 Free_space_in_MB FROM DBA_FREE_SPACE GROUP BY
tablespace_name) f WHERE t.tablespace_name= f.tablespace_name;



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, round(sum(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
order by 1;



By issuing below script you will extract the size & path of datafiles along with tablespace name, allocated space in MB, used space in MB & free space in MB.

set linesize 200
set pagesize 2000
COLUMN tablespace_name format a10
COLUMN file_name format a45
COLUMN free% format a7
SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
round(((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)),1) used_mb,
round(NVL (SUM (dfs.bytes) / 1024 / 1024, 0),2) free_space_mb
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME
ORDER BY df.tablespace_name;


Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply