Tablespace Info

SET LIN 120 PAGES 1000
COL SCHEMA_NAME FORMAT A20
COL USED_SPACE_GB FOR 999999999
COMPUTE SUM LABEL 'Grand Total in GB' OF USED_SPACE_GB ON REPORT
COMPUTE SUM LABEL 'Total for tablespace in GB' OF USED_SPACE_GB ON TABLESPACE_NAME 
  
BREAK ON TABLESPACE_NAME SKIP 1 ON REPORT
   
SELECT B.TABLESPACE_NAME,B.OWNER SCHEMA_NAME,ROUND(SUM(B.BYTES/1024/1024/1024)) USED_SPACE_GB,A.CREATED
FROM DBA_USERS A,DBA_SEGMENTS B
WHERE A.USERNAME=B.OWNER
GROUP BY B.OWNER,A.CREATED,B.TABLESPACE_NAME
ORDER BY B.TABLESPACE_NAME,ROUND(SUM(B.BYTES/1024/1024/1024)) DESC;

SET LIN 120 PAGES 1000
COL "Tablespace Name" HEADING 'Tablespace|Name' FOR A16
COL "Current Allocated(GB)" HEADING 'Current|Allocated(GB)' FOR 999999999
COL "Current Used(GB)" HEADING 'Current|Used(GB)' FOR 999999999
COL "Current Free(GB)" HEADING 'Current|Free(GB)' FOR 999999999
COL "Maximum Allocated(GB)" HEADING 'Maximum|Allocated(GB)' FOR 999999999
COL "Overall Free(GB)" HEADING 'Overall|Free(GB)' FOR 999999999
COL "Overall % Free" HEADING 'Overall|% Free'
COL "Status" FOR A8
COMPUTE SUM LABEL 'Total Size in GB' OF "Current Allocated(GB)" ON REPORT
COMPUTE SUM OF "Current Used(GB)" ON REPORT
COMPUTE SUM OF "Current Free(GB)" ON REPORT
COMPUTE SUM OF "Overall Free(GB)" ON REPORT
COMPUTE SUM OF "Maximum Allocated(GB)" ON REPORT

BREAK ON REPORT

SELECT
    TABLESPACE_NAME "Tablespace Name",
    ROUND(SUM(TOTAL_GB),2) "Current Allocated(GB)",
    ROUND(SUM(TOTAL_GB)-SUM(FREE_GB),2) "Current Used(GB)",
    ROUND(SUM(TOTAL_GB),2)-ROUND(SUM(TOTAL_GB)-SUM(FREE_GB),2) "Current Free(GB)",
    ROUND(SUM(MAX_GB),2) "Maximum Allocated(GB)",
    ROUND(SUM(MAX_GB)-(SUM(TOTAL_GB)-SUM(FREE_GB)),2) "Overall Free(GB)",
    ROUND((SUM(MAX_GB)-(SUM(TOTAL_GB)-SUM(FREE_GB)))/SUM(MAX_GB)*100) "Overall % Free",
    DECODE (TABLESPACE_NAME,'PERFSTAT','NULL',
    CASE 
        WHEN ROUND((SUM(MAX_GB)-(SUM(TOTAL_GB)-SUM(FREE_GB)))/SUM(MAX_GB)*100)<20 THEN 'RED'
        WHEN ROUND((SUM(MAX_GB)-(SUM(TOTAL_GB)-SUM(FREE_GB)))/SUM(MAX_GB)*100)<30 THEN 'YELLOW'
        ELSE 'GREEN'
        END
    ) "Status"
FROM (
    SELECT 
        TABLESPACE_NAME,
        SUM(BYTES)/1024/1024/1024 FREE_GB,
        0 TOTAL_GB,
        0 MAX_GB 
    FROM DBA_FREE_SPACE 
    GROUP BY TABLESPACE_NAME
    UNION 
        SELECT 
        TABLESPACE_NAME,
        0 CURRENT_GB,
        SUM(BYTES)/1024/1024/1024 TOTAL_GB,
        SUM(DECODE(MAXBYTES,0,BYTES,MAXBYTES))/1024/1024/1024 MAX_GB
    FROM DBA_DATA_FILES 
    GROUP BY TABLESPACE_NAME
    UNION
    SELECT 
        B.TABLESPACE_NAME,
        B.TOTAL_GB-SUM(A.BLOCKS*B.BLOCK_SIZE)/1024/1024/1024 FREE_GB,
        0 TOTAL_GB,
        0 MAX_GB 
    FROM V$TEMPSEG_USAGE A,(
        SELECT
            B.TABLESPACE_NAME,
            B.BLOCK_SIZE,
            SUM (C.BYTES)/1024/1024/1024 TOTAL_GB
        FROM DBA_TABLESPACES B,DBA_TEMP_FILES C
        WHERE B.TABLESPACE_NAME=C.TABLESPACE_NAME 
        GROUP BY B.TABLESPACE_NAME,B.BLOCK_SIZE  
    ) B
    GROUP BY B.TABLESPACE_NAME,B.TOTAL_GB
    UNION 
    SELECT 
        TABLESPACE_NAME,
        0 CURRENT_GB,
        SUM(BYTES)/1024/1024/1024 TOTAL_GB,
        SUM(DECODE(MAXBYTES,0,BYTES,MAXBYTES))/1024/1024/1024 MAX_GB
    FROM DBA_TEMP_FILES 
    GROUP BY TABLESPACE_NAME
) 
GROUP BY TABLESPACE_NAME
ORDER BY 1;