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

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.

One thought on “Script to extract size of Oracle tablespaces & associated datafiles”

  1. thanks you for the work. it has been very usefull for me.

    there is a Sintax error in line
    round(((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)),1) used_mb,
    it must be only two ‘(‘ after “round”
    the right full script would be:

    ====================================
    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,
    df.bytes / 1024 / 1024 used_mb0,
    nvl(sum (dfs.bytes)/1024/1024, 0) used_mb1,
    (df.bytes / 1024 / 1024) – (nvl(sum (dfs.bytes)/1024/1024, 0)) used_mb2,
    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;

Leave a Reply