Tablespace Usage QUERY

CHECK THE TABLESPACE USAGE

rem SET TERMOUT OFF
rem
set linesi 132
set pagesi 1000
column tablespace format a30 heading Tablespace
column count(*) format 999,999 heading 'Blks Count'
column total_kb format 999,999,999 heading 'Total KB'
column free_kb format 99,999,999 heading 'Free KB'
column max_kb format 9,999,999 heading 'Max Free KB'
column min_kb format 9,999,999 heading 'Min Free KB'
column PerFree format 999 heading '%Free'
column Threshold format 999 heading 'Limit'
column Ext_count format 99,999 heading 'Ext Count'
rem
BREAK ON REPORT
COMPUTE SUM OF TOTAL_KB ON REPORT
COMPUTE SUM OF FREE_KB ON REPORT
rem
SET TERMOUT ON
SELECT ROUND((DECODE(A.FREE_BYTES,NULL,0,A.FREE_BYTES)/B.TOTAL_BYTES) * 100) PerFree,
       C.THRESHOLD Threshold,
       RPAD(B.TABLESPACE_NAME,30,'.') tablespace,
       B.TOTAL_BYTES/1024 total_kb,
       NVL(A.FREE_BYTES/1024, 0) free_kb, 
       A.MAX_BYTES/1024 max_kb, 
       A.MIN_BYTES/1024 min_kb,
       EXT_COUNT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES,
              MAX(BYTES) max_BYTES, 
              MIN(BYTES) min_BYTES,
              count(*) EXT_COUNT
              FROM DBA_FREE_SPACE
              GROUP BY TABLESPACE_NAME) A, 
      (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_BYTES
              FROM DBA_DATA_FILES 
              GROUP BY TABLESPACE_NAME )B,
      (select tablespace_name, threshold from OSA_ORACLE.tbs ) c
WHERE A.TABLESPACE_NAME(+) = B.TABLESPACE_NAME
and   C.TABLESPACE_NAME(+) = A.TABLESPACE_NAME

ORDER BY B.TABLESPACE_NAME;