Script to extract size of Oracle tablespaces & associated datafiles

Published by Jignesh Jethwa
Oracle 11g Logo

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(%)

Syntax:
COLU Free% FORMA A10
COLUMN tablespace_name FORMAT A17
SELECT t.tablespace_name, t.total_space_in_MB, f.free_space_in_MB,
TO_CHAR((f.free_space_in_MB*100/t.total_space_in_MB),'99990.000')
"Free%"
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;

OR

 

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.

Syntax:
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