Tablespace USAGE. FREE space Used space

Tablespace Usage

SET LIN 120 PAGES 1000COL "Tablespace Name" HEADING 'Tablespace|Name' FOR A16COL "Current Allocated(GB)" HEADING 'Current|Allocated(GB)' FOR 999999999COL "Current Used(GB)" HEADING 'Current|Used(GB)' FOR 999999999COL "Current Free(GB)" HEADING 'Current|Free(GB)' FOR 999999999COL "Maximum Allocated(GB)" HEADING 'Maximum|Allocated(GB)' FOR 999999999COL "Overall Free(GB)" HEADING 'Overall|Free(GB)' FOR 999999999COL "Overall % Free" HEADING 'Overall|% Free'COL "Status" FOR A8COMPUTE SUM LABEL 'Total Size in GB' OF "Current Allocated(GB)" ON REPORTCOMPUTE SUM OF "Current Used(GB)" ON REPORTCOMPUTE SUM OF "Current Free(GB)" ON REPORTCOMPUTE SUM OF "Overall Free(GB)" ON REPORTCOMPUTE 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_NAMEORDER BY 1;

OUTPUT

Tablespace             Current    Current    Current       Maximum    Overall    Overall
Name             Allocated(GB)   Used(GB)   Free(GB) Allocated(GB)   Free(GB)     % Free Status
---------------- ------------- ---------- ---------- ------------- ---------- ---------- --------
SYSAUX                       2          2          0            32         30         93 GREEN
SYSTEM                       4          4          0            32         28         89 GREEN
TEMP                         4          0          4            32         32        100 GREEN
UNDOTBS1                    32          7         25            32         25         78 GREEN
USERS                        4          0          4             4          4        100 GREEN
                 ------------- ---------- ---------- ------------- ----------

Total Size in GB           142         51         91           228        178