16 June 2010

show any tablespaces that are above 75% usage

SELECT t.tablespace_name,
(tsa.bytes / 1024)/1024 AS "Size (MB)",
(round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf
WHERE t.tablespace_name = tsa.tablespace_name
AND ((round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) > 75)
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name

No comments:

Post a Comment