Sunday, January 24, 2010

Get tablespaces usage


SELECT t1.TABLESPACE_NAME AS "Tablespace Name",
t1.BYTES AS "Bytes Used",
t2.BYTES AS "Bytes Free",
t2.largest AS "Largest",
ROUND(((t1.BYTES - t2.BYTES)/t1.BYTES)*100,2) AS "% Used"
FROM
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
) t1,
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES ,
MAX(BYTES) largest
FROM dba_free_space
GROUP BY TABLESPACE_NAME
) t2
WHERE t1.TABLESPACE_NAME = t2.TABLESPACE_NAME
ORDER BY ((t1.BYTES - t2.BYTES)/t1.BYTES) DESC

No comments:

Post a Comment

Oracle Query