Download(tsusage.sql)
REM +======================================================================+ REM REM File Name: tsusage.sql REM REM Description: REM Query To Check Database Tablespace Usage With Percentage REM This Query Also Takes Care If the Datafile Autoextend is ON. REM REM Notes: REM Usage: sqlplus "/ as sysdba" @tsusage.sql REM REM +======================================================================+ clear columns set lines 180 set pages 100 select distinct a.tablespace_name, SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Total (MB)", (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Used (MB)", round(((SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Used", (SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Free (MB)", round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Free" --round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent" from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c where a.file_id = b.file#(+) and a.tablespace_name = c.tablespace_name GROUP by a.tablespace_name, c."Free"/1024 --HAVING round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) <= 10 order by round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) asc /
This comment has been removed by the author.
ReplyDeleteVery useful blog for new learners of core and apps DBA, really appreciable. Thank you for sharing your knowledge.
Delete