Tuesday, 7 June 2011

Oracle Tablespace size monitoring

COLUMN pct_free FORMAT 999.99 HEADING "% Free"
COLUMN name FORMAT A16 HEADING "Tablespace Name"
COLUMN mbytes FORMAT 99,999,999 HEADING "Total GBytes"
COLUMN used FORMAT 99,999,999 HEADING "Used Gbytes"
COLUMN free FORMAT 99,999,999 HEADING "Free Gbytes"

SELECT  fs.tablespace_name name,
        round((df.totalspace/1024),2) mbytes,
        round((df.totalspace - fs.freespace)/1024,2) used,
        round(fs.freespace/1024,2) free,
        round(100 * (fs.freespace / df.totalspace),2) pct_free
FROM    (SELECT
         tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
    FROM
    dba_data_files
    GROUP BY
    tablespace_name
    ) df,
    (SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1048576) FreeSpace
    FROM
    dba_free_space
    GROUP BY  tablespace_name
    ) fs
WHERE   df.tablespace_name = fs.tablespace_name(+)
order by 2 desc;

No comments:

Post a Comment

Number of Visitors