find tablespaces consuming more then 85% space


find tablespaces consuming more then 85% space:

=======================================================

set lines 999
set pagesize 999
select tablespace_name, ROUND(bytes_free,2) "Free Space (MB)" , ROUND(bytes_total-bytes_free,2) "Used Space (MB)",
ROUND(bytes_total,2) "Total Size (MB)", perused "% Used", 100-perused "% Free" from
(select a.tablespace_name, a.bytes_free,b.bytes_total, ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 85 and tablespace_name not like '%UNDO%'  ORDER BY 6 desc;

No comments: