Tablespace Utilization Report

-- Enhanced script for Oracle Database tablespace utilization report.

-- Enhancements include:

--   - Added database name and total occupied/free space as header.

--   - Added column for segment-based used space in overall summary for accuracy comparison.

--   - Simplified and improved temporary tablespace utilization using DBA_TEMP_FREE_SPACE (Oracle 11g+).

--   - Added free space fragmentation report.

--   - Limited segment level report to top 20 largest objects for brevity.

--   - Added partition_name to segment report for more granularity.

--   - Included UNDO tablespaces in overall reports (already present, but confirmed).

--   - Improved column formatting and added totals where feasible.

--   - Adjusted GROUP BY in Section 1 using CTE for cleaner aggregation (fixes ORA-00979).

--   - Added new Section 6 for real-time temporary tablespace usage via GV$TEMP_SPACE_HEADER.

--   - Removed ASM Diskgroup Utilization section as ASM is not used.

--   - Fixed typo in Section 2 CASE statement (removed stray '|').

--   - Fixed typo in Section 3 owner exclusion list (removed em dash after APPQOSSYS).

--   - Fixed ORA-00937 in Database Summary by using a cross join for DB_NAME.

--   - Fixed ORA-00900 in Section 4 by correcting 'VAL COLUMN' to 'COLUMN'.

--   - Ensured all queries are compatible with Oracle 11g and later.

--

-- Ensure you have SELECT privileges on DBA_TABLESPACES, DBA_DATA_FILES,

-- DBA_FREE_SPACE, DBA_SEGMENTS, DBA_TEMP_FILES, DBA_TEMP_FREE_SPACE,

-- GV$TEMP_SPACE_HEADER, V$DATABASE, and GV$/V$ views as needed.

--

-- Compatible with Oracle 11g and later. For earlier versions, DBA_TEMP_FREE_SPACE may not be available.

--

-- Set SQL*Plus formatting options for better readability

SET PAGESIZE 100

SET LINESIZE 200

SET FEEDBACK ON

SET VERIFY OFF

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- Database Summary                                                           --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '

COLUMN DB_NAME FORMAT A30

COLUMN "Total Allocated (GB)" FORMAT 999,999,999.99 HEADING 'Total Allocated|Space (GB)'

COLUMN "Total Used (GB)" FORMAT 999,999,999.99 HEADING 'Total Used|Space (GB)'

COLUMN "Total Free (GB)" FORMAT 999,999,999.99 HEADING 'Total Free|Space (GB)'

COLUMN "Total Used Pct (%)" FORMAT 999.99 HEADING 'Total DB|Used Pct (%)'

SELECT

    d.name AS DB_NAME,

    ROUND(SUM(df.bytes) / (1024 * 1024 * 1024), 2) AS "Total Allocated (GB)",

    ROUND(SUM(df.bytes - NVL(fs.bytes, 0)) / (1024 * 1024 * 1024), 2) AS "Total Used (GB)",

    ROUND(SUM(NVL(fs.bytes, 0)) / (1024 * 1024 * 1024), 2) AS "Total Free (GB)",

    ROUND((SUM(df.bytes - NVL(fs.bytes, 0)) / SUM(df.bytes)) * 100, 2) AS "Total Used Pct (%)"

FROM

    dba_data_files df

LEFT JOIN (

    SELECT

        file_id,

        SUM(bytes) AS bytes

    FROM

        dba_free_space

    GROUP BY

        file_id

) fs ON df.file_id = fs.file_id

CROSS JOIN (

    SELECT name FROM v$database

) d

WHERE

    df.tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents != 'TEMPORARY')

GROUP BY

    d.name;

CLEAR COLUMNS

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- 1. Overall Tablespace Utilization (Summary with Autoextend and Segments)   --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '

COLUMN TABLESPACE_NAME FORMAT A30

COLUMN STATUS FORMAT A10

COLUMN "Total Size (GB)" FORMAT 999,999,999.99 HEADING 'Allocated|Size (GB)'

COLUMN "Used Space (GB)" FORMAT 999,999,999.99 HEADING 'Used (from Free)|Space (GB)'

COLUMN "Segments Used (GB)" FORMAT 999,999,999.99 HEADING 'Segments|Used (GB)'

COLUMN "Free Space (GB)" FORMAT 999,999,999.99 HEADING 'Free|Space (GB)'

COLUMN "Allocated Pct Used (%)" FORMAT 999.99 HEADING 'Allocated|Pct Used (%)'

COLUMN "Max Possible Size (GB)" FORMAT 999,999,999.99 HEADING 'Max Possible|Size (GB)'

COLUMN "Max Possible Pct Used (%)" FORMAT 999.99 HEADING 'Max Possible|Pct Used (%)'

WITH segment_space AS (

    SELECT

        tablespace_name,

        ROUND(SUM(bytes) / (1024 * 1024 * 1024), 2) AS segment_used_gb

    FROM

        dba_segments

    GROUP BY

        tablespace_name

),

file_space AS (

    SELECT

        t.tablespace_name,

        t.status,

        SUM(f.bytes) / (1024 * 1024 * 1024) AS total_size_gb,

        SUM(f.bytes - NVL(s.bytes, 0)) / (1024 * 1024 * 1024) AS used_space_gb,

        SUM(NVL(s.bytes, 0)) / (1024 * 1024 * 1024) AS free_space_gb,

        SUM(DECODE(f.autoextensible, 'YES', f.maxbytes, f.bytes)) / (1024 * 1024 * 1024) AS max_size_gb

    FROM

        dba_tablespaces t

    JOIN

        dba_data_files f ON t.tablespace_name = f.tablespace_name

    LEFT JOIN (

        SELECT

            file_id,

            SUM(bytes) AS bytes

        FROM

            dba_free_space

        GROUP BY

            file_id

    ) s ON f.file_id = s.file_id

    WHERE

        t.contents != 'TEMPORARY'

    GROUP BY

        t.tablespace_name, t.status

)

SELECT

    fs.tablespace_name,

    fs.status,

    ROUND(fs.total_size_gb, 2) AS "Total Size (GB)",

    ROUND(fs.used_space_gb, 2) AS "Used Space (GB)",

    NVL(ss.segment_used_gb, 0) AS "Segments Used (GB)",

    ROUND(fs.free_space_gb, 2) AS "Free Space (GB)",

    ROUND((fs.used_space_gb / fs.total_size_gb) * 100, 2) AS "Allocated Pct Used (%)",

    ROUND(fs.max_size_gb, 2) AS "Max Possible Size (GB)",

    ROUND((fs.used_space_gb / fs.max_size_gb) * 100, 2) AS "Max Possible Pct Used (%)"

FROM

    file_space fs

LEFT JOIN

    segment_space ss ON fs.tablespace_name = ss.tablespace_name

ORDER BY

    "Allocated Pct Used (%)" DESC;

CLEAR COLUMNS

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- 2. Tablespace Utilization by Datafile (Granular)                           --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '

COLUMN TABLESPACE_NAME FORMAT A25

COLUMN FILE_NAME FORMAT A60

COLUMN "Current Size (GB)" FORMAT 999,999.99 HEADING 'Current|Size (GB)'

COLUMN "Used Space (GB)" FORMAT 999,999.99 HEADING 'Used|Space (GB)'

COLUMN "Free Space (GB)" FORMAT 999,999.99 HEADING 'Free|Space (GB)'

COLUMN "Used Pct (%)" FORMAT 999.99 HEADING 'Used|Pct (%)'

COLUMN "Autoextensible" FORMAT A14 HEADING 'Autoextend?'

COLUMN "Max Size (GB)" FORMAT 999,999.99 HEADING 'Max Possible|Size (GB)'

SELECT

    df.tablespace_name,

    df.file_name,

    ROUND(df.bytes / (1024 * 1024 * 1024), 2) AS "Current Size (GB)",

    ROUND((df.bytes - NVL(fs.bytes, 0)) / (1024 * 1024 * 1024), 2) AS "Used Space (GB)",

    ROUND(NVL(fs.bytes, 0) / (1024 * 1024 * 1024), 2) AS "Free Space (GB)",

    ROUND(((df.bytes - NVL(fs.bytes, 0)) / df.bytes) * 100, 2) AS "Used Pct (%)",

    df.autoextensible,

    CASE

        WHEN df.autoextensible = 'YES' THEN ROUND(df.maxbytes / (1024 * 1024 * 1024), 2)

        ELSE NULL

    END AS "Max Size (GB)"

FROM

    dba_data_files df

LEFT JOIN (

    SELECT

        file_id,

        SUM(bytes) AS bytes

    FROM

        dba_free_space

    GROUP BY

        file_id

) fs ON df.file_id = fs.file_id

ORDER BY

    df.tablespace_name, df.file_id;

CLEAR COLUMNS

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- 3. Segment Level Utilization (Top 20 Largest Objects)                      --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '

COLUMN OWNER FORMAT A20

COLUMN SEGMENT_NAME FORMAT A30

COLUMN PARTITION_NAME FORMAT A30

COLUMN SEGMENT_TYPE FORMAT A15

COLUMN TABLESPACE_NAME FORMAT A25

COLUMN "Size (GB)" FORMAT 999,999,999.99 HEADING 'Allocated|Size (GB)'

SELECT

    owner,

    segment_name,

    partition_name,

    segment_type,

    tablespace_name,

    "Size (GB)"

FROM (

    SELECT

        owner,

        segment_name,

        partition_name,

        segment_type,

        tablespace_name,

        ROUND(bytes / (1024 * 1024 * 1024), 2) AS "Size (GB)"

    FROM

        dba_segments

    WHERE

        tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents != 'TEMPORARY')

    AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'OUTLN', 'APPQOSSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'DIP', 'ORACLE_OCM', 'AUDSYS', 'XDB', 'MDSYS', 'OLAPSYS', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'WMSYS', 'EXFSYS', 'CTXSYS', 'ANONYMOUS', 'APEX_%', 'FLOWS_FILES', 'MGMT_VIEW', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SI_INFORMTN_SCHEMA', 'XS$NULL', 'MDDATA', 'OJVMSYS', 'APEX_PUBLIC_USER', 'AUDSYS', 'GGSYS', 'DVF', 'DVSYS', 'LBAC_SYS', 'CDB_ONLY_SYS')

    ORDER BY

        bytes DESC

)

WHERE ROWNUM <= 20;

CLEAR COLUMNS

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- 4. Temporary Tablespace Utilization (Using DBA_TEMP_FREE_SPACE)            --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '

COLUMN TABLESPACE_NAME FORMAT A25

COLUMN "Allocated Size (GB)" FORMAT 999,999.99 HEADING 'Allocated|Size (GB)'

COLUMN "Used Space (GB)" FORMAT 999,999.99 HEADING 'Used|Space (GB)'

COLUMN "Free Space (GB)" FORMAT 999,999.99 HEADING 'Free|Space (GB)'

COLUMN "Used Pct (%)" FORMAT 999.99 HEADING 'Used|Pct (%)'

COLUMN "Max Possible Size (GB)" FORMAT 999,999.99 HEADING 'Max Possible|Size (GB)'

SELECT

    fs.tablespace_name,

    ROUND(fs.allocated_space / (1024 * 1024 * 1024), 2) AS "Allocated Size (GB)",

    ROUND((fs.allocated_space - fs.free_space) / (1024 * 1024 * 1024), 2) AS "Used Space (GB)",

    ROUND(fs.free_space / (1024 * 1024 * 1024), 2) AS "Free Space (GB)",

    ROUND(((fs.allocated_space - fs.free_space) / fs.allocated_space) * 100, 2) AS "Used Pct (%)",

    ROUND(SUM(DECODE(tf.autoextensible, 'YES', tf.maxbytes, tf.bytes)) / (1024 * 1024 * 1024), 2) AS "Max Possible Size (GB)"

FROM

    dba_temp_free_space fs

JOIN

    dba_temp_files tf ON fs.tablespace_name = tf.tablespace_name

GROUP BY

    fs.tablespace_name, fs.allocated_space, fs.free_space

ORDER BY

    "Used Pct (%)" DESC;

CLEAR COLUMNS

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- 5. Free Space Fragmentation by Tablespace                                  --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '

COLUMN TABLESPACE_NAME FORMAT A25

COLUMN "Fragments" FORMAT 999,999 HEADING 'Number of|Fragments'

COLUMN "Largest Chunk (GB)" FORMAT 999,999.99 HEADING 'Largest|Chunk (GB)'

COLUMN "Total Free (GB)" FORMAT 999,999.99 HEADING 'Total|Free (GB)'

SELECT

    tablespace_name,

    COUNT(*) AS "Fragments",

    ROUND(MAX(bytes) / (1024 * 1024 * 1024), 2) AS "Largest Chunk (GB)",

    ROUND(SUM(bytes) / (1024 * 1024 * 1024), 2) AS "Total Free (GB)"

FROM

    dba_free_space

GROUP BY

    tablespace_name

ORDER BY

    "Fragments" DESC;

CLEAR COLUMNS

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- 6. Real-Time Temporary Tablespace Usage (Using GV$TEMP_SPACE_HEADER)       --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '

COLUMN TABLESPACE_NAME FORMAT A25

COLUMN "Total Temp Size (GB)" FORMAT 999,999.99 HEADING 'Total Temp|Size (GB)'

COLUMN "Used Temp Space (GB)" FORMAT 999,999.99 HEADING 'Used Temp|Space (GB)'

COLUMN "Free Temp Space (GB)" FORMAT 999,999.99 HEADING 'Free Temp|Space (GB)'

COLUMN "Used Pct (%)" FORMAT 999.99 HEADING 'Used|Pct (%)'

SELECT

    ts.tablespace_name,

    ROUND(SUM(tf.bytes) / (1024 * 1024 * 1024), 2) AS "Total Temp Size (GB)",

    ROUND(SUM(tu.used_space) / (1024 * 1024 * 1024), 2) AS "Used Temp Space (GB)",

    ROUND(SUM(tf.bytes - tu.used_space) / (1024 * 1024 * 1024), 2) AS "Free Temp Space (GB)",

    ROUND((SUM(tu.used_space) / SUM(tf.bytes)) * 100, 2) AS "Used Pct (%)"

FROM

    dba_temp_files tf

JOIN

    dba_tablespaces ts ON tf.tablespace_name = ts.tablespace_name

LEFT JOIN (

    SELECT

        tablespace_name,

        SUM(bytes_used) AS used_space

    FROM

        gv$temp_space_header

    GROUP BY

        tablespace_name

) tu ON ts.tablespace_name = tu.tablespace_name

WHERE

    ts.contents = 'TEMPORARY'

GROUP BY

    ts.tablespace_name

ORDER BY

    ts.tablespace_name;

CLEAR COLUMNS

SET PAGESIZE 14

SET LINESIZE 80

SET FEEDBACK ON

PROMPT ' '

PROMPT '--------------------------------------------------------------------------------'

PROMPT '-- Enhanced script execution completed.                                       --'

PROMPT '--------------------------------------------------------------------------------'

PROMPT ' '