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;
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