Query To Check Database Tablespace Usage With Percentage

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
/