SSL Port or not

BEGIN

    -- Enable DBMS_OUTPUT

    DBMS_OUTPUT.ENABLE(1000000);

    DECLARE

        -- Variables to prevent buffer issues

        l_db_name       VARCHAR2(255);

        l_instance_name VARCHAR2(255);

        l_host_name     VARCHAR2(255);

        l_ssl_status    VARCHAR2(255);

        v_db_version    VARCHAR2(30);

        v_data_found    BOOLEAN := FALSE;

        v_output_width  CONSTANT NUMBER := 88; -- 20+2+20+2+20+2+30 = 88 chars

    BEGIN

        -- Debug: Confirm execution start

        DBMS_OUTPUT.PUT_LINE('Executing query...');

        -- Check version

        SELECT banner INTO v_db_version

        FROM v$version

        WHERE ROWNUM = 1;

        -- Header

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));

        DBMS_OUTPUT.PUT_LINE(RPAD('-- Database Connection Info', v_output_width - 2, ' ') || '--');

        DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('DB Name', 20) || '  ' ||

                             RPAD('Instance Name', 20) || '  ' ||

                             RPAD('Host Name', 20) || '  ' ||

                             RPAD('SSL', 30));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 20, '-') || '  ' ||

                             RPAD('-', 20, '-') || '  ' ||

                             RPAD('-', 20, '-') || '  ' ||

                             RPAD('-', 30, '-'));

        -- Fetch data

        FOR rec IN (

            SELECT

                d.name,

                i.instance_name,

                i.host_name,

                CASE

                    WHEN SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') = 'tcps'

                    THEN 'Yes (Port 5433, SSL enabled)'

                    ELSE 'No (SSL not enabled)'

                END AS ssl_status

            FROM

                v$database d,

                v$instance i,

                v$session s

            WHERE

                s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1)

        ) LOOP

            v_data_found := TRUE;

            l_db_name := SUBSTR(rec.name, 1, 255);

            l_instance_name := SUBSTR(rec.instance_name, 1, 255);

            l_host_name := SUBSTR(rec.host_name, 1, 255);

            l_ssl_status := SUBSTR(rec.ssl_status, 1, 255);

            DBMS_OUTPUT.PUT_LINE(RPAD(l_db_name, 20) || '  ' ||

                                 RPAD(l_instance_name, 20) || '  ' ||

                                 RPAD(l_host_name, 20) || '  ' ||

                                 RPAD(l_ssl_status, 30));

        END LOOP;

        -- No data diagnostic

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE(' ');

            DBMS_OUTPUT.PUT_LINE('No data found. Check privileges on V$DATABASE, V$INSTANCE, V$SESSION or session data.');

        END IF;

        -- 11g diagnostic

        IF v_db_version LIKE '%11%' THEN

            DBMS_OUTPUT.PUT_LINE(' ');

            DBMS_OUTPUT.PUT_LINE('Note: SSL detection via NETWORK_SERVICE_BANNER is unavailable in Oracle 11g. Using SYS_CONTEXT(''USERENV'', ''NETWORK_PROTOCOL'').');

            DBMS_OUTPUT.PUT_LINE('If SSL status is incorrect, verify listener.ora for TCPS configuration.');

        END IF;

        -- Footer

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));

        DBMS_OUTPUT.PUT_LINE(RPAD('-- Report Completed', v_output_width - 2, ' ') || '--');

        DBMS_OUTPUT.PUT_LINE(RPAD('-', v_output_width, '-'));

        DBMS_OUTPUT.PUT_LINE(' ');

    EXCEPTION

        WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE(' ');

            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

            DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');

            RAISE;

    END;

END;

/

/*

 * SQL script with SELECT statements to report database name, instance name, hostname, and SSL connection status.

 * Output is formatted for SQL*Plus/Toad with aligned columns using COLUMN commands.

 * Features:

 *   - Retrieves DB Name from V$DATABASE.

 *   - Retrieves Instance Name and Host Name from V$INSTANCE.

 *   - Checks SSL status using SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') for tcps (SSL).

 *   - Reports SSL as 'Yes (Port 5433, SSL enabled)' or 'No (SSL not enabled)'.

 *   - Includes diagnostic notes for Oracle 11g where SSL detection is limited.

 * Requires SELECT privileges on V$DATABASE, V$INSTANCE, V$SESSION, and V$MYSTAT.

 * Compatible with Oracle 11g and later.

 * To ensure output visibility and alignment:

 *   - SQL*Plus: Run `SET LINESIZE 100; SET PAGESIZE 50;` before executing.

 *   - Toad: Ensure output window width is at least 100 characters.

 * If no output appears, check:

 *   - Sufficient privileges on V$DATABASE, V$INSTANCE, V$SESSION.

 *   - Valid session data in V$SESSION.

 * If SSL status is incorrect, verify listener.ora for TCPS configuration or provide custom SSL indicator.

 */

-- Set SQL*Plus formatting for alignment

SET LINESIZE 100

SET PAGESIZE 50

SET FEEDBACK OFF

-- Column formatting for aligned output

COLUMN db_name FORMAT A20 HEADING 'DB Name'

COLUMN instance_name FORMAT A20 HEADING 'Instance Name'

COLUMN host_name FORMAT A20 HEADING 'Host Name'

COLUMN ssl_status FORMAT A30 HEADING 'SSL'

-- Header

PROMPT 

PROMPT ------------------------------------------------------------

PROMPT -- Database Connection Info                                --

PROMPT ------------------------------------------------------------

-- Main query

SELECT

    d.name AS db_name,

    i.instance_name,

    i.host_name,

    CASE

        WHEN SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') = 'tcps'

        THEN 'Yes (Port 5433, SSL enabled)'

        ELSE 'No (SSL not enabled)'

    END AS ssl_status

FROM

    v$database d,

    v$instance i,

    v$session s

WHERE

    s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1); -- Current session

-- Diagnostic note for Oracle 11g

SELECT

    CASE

        WHEN banner LIKE '%11%'

        THEN 'Note: SSL detection via NETWORK_SERVICE_BANNER is unavailable in Oracle 11g. Using SYS_CONTEXT(''USERENV'', ''NETWORK_PROTOCOL''). ' ||

             'If SSL status is incorrect, verify listener.ora for TCPS configuration or provide custom SSL indicator.'

        ELSE NULL

    END AS diagnostic

FROM

    v$version

WHERE

    ROWNUM = 1;

-- Footer

PROMPT 

PROMPT ------------------------------------------------------------

PROMPT -- Report Completed                                       --

PROMPT ------------------------------------------------------------

-- Reset SQL*Plus settings

CLEAR COLUMNS

SET FEEDBACK ON

SET LINESIZE 80

SET PAGESIZE 14

PL/SQL - Tablespace Utilization Report

/*

 * Comprehensive PL/SQL script for Oracle Database storage and space utilization reporting.

 * Output is formatted for SQL*Plus/Toad using DBMS_OUTPUT with meticulously aligned columns.

 * Enhancements include:

 *   - Database name and total occupied/free space as header.

 *   - Segment-based used space in overall summary for accuracy comparison.

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

 *   - Free space fragmentation report.

 *   - Segment level report limited to top 20 largest objects (using ROWNUM for Oracle 11g+ compatibility).

 *   - Partition_name included in segment report for granularity.

 *   - UNDO tablespaces included in overall reports.

 *   - Improved column formatting with totals where feasible.

 *   - Fixed ORA-00979 in Section 1 using CTE for cleaner aggregation.

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

 *   - Removed ASM Diskgroup Utilization (ASM not used).

 *   - Fixed ORA-00937 (Database Summary), ORA-00900 (Section 4), and ORA-00979 (Section 1).

 *   - Adjusted DBMS_OUTPUT padding for perfect alignment.

 *

 * Ensure 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.

 * Compatible with Oracle 11g and later.

 * To ensure output visibility:

 *   - SQL*Plus: Run `SET SERVEROUTPUT ON SIZE 1000000; SET LINESIZE 200;` before executing.

 *   - Toad: Enable DBMS Output pane and set display width to at least 200 characters.

 * If no output or misalignment occurs, check:

 *   - DBMS_OUTPUT enabled.

 *   - Sufficient privileges.

 *   - Data in queried views (e.g., non-temporary tablespaces, segments).

 */

BEGIN

    -- Enable DBMS_OUTPUT with a large buffer

    DBMS_OUTPUT.ENABLE(1000000);

    -- Declare cursors for each report section

    DECLARE

        -- Cursor for Database Summary (Section 0)

        CURSOR db_summary_cur IS

            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;

        -- Cursor for Overall Tablespace Utilization (Section 1)

        CURSOR overall_tablespace_cur IS

            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_size_gb,

                ROUND((fs.used_space_gb / fs.max_size_gb) * 100, 2) AS max_pct_used

            FROM

                file_space fs

            LEFT JOIN

                segment_space ss ON fs.tablespace_name = ss.tablespace_name

            ORDER BY

                allocated_pct_used DESC;

        -- Cursor for Tablespace Utilization by Datafile (Section 2)

        CURSOR datafile_util_cur IS

            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;

        -- Cursor for Segment Level Utilization (Top 20 Largest Objects, Section 3)

        CURSOR segment_util_cur IS

            SELECT

                owner,

                segment_name,

                partition_name,

                segment_type,

                tablespace_name,

                ROUND(bytes / (1024 * 1024 * 1024), 2) AS size_gb

            FROM (

                SELECT

                    owner,

                    segment_name,

                    partition_name,

                    segment_type,

                    tablespace_name,

                    bytes

                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; -- Compatible with Oracle 11g+

        -- Cursor for Temporary Tablespace Utilization (Section 4)

        CURSOR temp_tablespace_cur IS

            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_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;

        -- Cursor for Free Space Fragmentation (Section 5)

        CURSOR fragmentation_cur IS

            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;

        -- Cursor for Real-Time Temporary Tablespace Usage (Section 6)

        CURSOR realtime_temp_cur IS

            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;

        -- Variable to track if data was found for each section

        v_data_found BOOLEAN;

    BEGIN

        -- Section 0: Database Summary

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- Database Summary                                                           --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('DB_NAME', 30) || RPAD('Total Allocated(GB)', 20) || 

                             RPAD('Total Used(GB)', 18) || RPAD('Total Free(GB)', 18) || 

                             RPAD('Used Pct(%)', 12));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 30, '-') || ' ' || RPAD('-', 18, '-') || ' ' || 

                             RPAD('-', 16, '-') || ' ' || RPAD('-', 16, '-') || ' ' || 

                             RPAD('-', 10, '-')); -- Total: 96 chars

        v_data_found := FALSE;

        FOR rec IN db_summary_cur LOOP

            v_data_found := TRUE;

            DBMS_OUTPUT.PUT_LINE(RPAD(rec.db_name, 30) || 

                                 RPAD(TO_CHAR(rec.total_allocated_gb, '999,999,999.99'), 20) || 

                                 RPAD(TO_CHAR(rec.total_used_gb, '999,999,999.99'), 18) || 

                                 RPAD(TO_CHAR(rec.total_free_gb, '999,999,999.99'), 18) || 

                                 RPAD(TO_CHAR(rec.total_used_pct, '999.99'), 12));

        END LOOP;

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE('No data found for Database Summary. Check privileges or data in DBA_DATA_FILES/DBA_TABLESPACES.');

        END IF;

        -- Section 1: Overall Tablespace Utilization

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- 1. Overall Tablespace Utilization (Summary with Autoextend and Segments)   --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('TABLESPACE_NAME', 25) || RPAD('STATUS', 10) || 

                             RPAD('Allocated(GB)', 15) || RPAD('Used(GB)', 15) || 

                             RPAD('Segments(GB)', 15) || RPAD('Free(GB)', 15) || 

                             RPAD('Used Pct(%)', 12) || RPAD('Max Size(GB)', 15) || 

                             RPAD('Max Pct(%)', 12));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || ' ' || RPAD('-', 8, '-') || ' ' || 

                             RPAD('-', 13, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 13, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 10, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 10, '-')); -- Total: 124 chars

        v_data_found := FALSE;

        FOR rec IN overall_tablespace_cur LOOP

            v_data_found := TRUE;

            DBMS_OUTPUT.PUT_LINE(RPAD(rec.tablespace_name, 25) || 

                                 RPAD(rec.status, 10) || 

                                 RPAD(TO_CHAR(rec.total_size_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.used_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.segments_used_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.free_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.allocated_pct_used, '999.99'), 12) || 

                                 RPAD(TO_CHAR(rec.max_size_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.max_pct_used, '999.99'), 12));

        END LOOP;

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE('No data found for Overall Tablespace Utilization. Check privileges or data in DBA_TABLESPACES/DBA_DATA_FILES.');

        END IF;

        -- Section 2: Tablespace Utilization by Datafile

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- 2. Tablespace Utilization by Datafile (Granular)                           --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('TABLESPACE_NAME', 25) || RPAD('FILE_NAME', 50) || 

                             RPAD('Current Size(GB)', 18) || RPAD('Used(GB)', 15) || 

                             RPAD('Free(GB)', 15) || RPAD('Used Pct(%)', 12) || 

                             RPAD('Autoextend?', 12) || RPAD('Max Size(GB)', 15));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || ' ' || RPAD('-', 48, '-') || ' ' || 

                             RPAD('-', 16, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 13, '-') || ' ' || RPAD('-', 10, '-') || ' ' || 

                             RPAD('-', 10, '-') || ' ' || RPAD('-', 13, '-')); -- Total: 152 chars

        v_data_found := FALSE;

        FOR rec IN datafile_util_cur LOOP

            v_data_found := TRUE;

            DBMS_OUTPUT.PUT_LINE(RPAD(rec.tablespace_name, 25) || 

                                 RPAD(SUBSTR(rec.file_name, 1, 50), 50) || 

                                 RPAD(TO_CHAR(rec.current_size_gb, '999,999.99'), 18) || 

                                 RPAD(TO_CHAR(rec.used_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.free_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.used_pct, '999.99'), 12) || 

                                 RPAD(rec.autoextensible, 12) || 

                                 RPAD(NVL(TO_CHAR(rec.max_size_gb, '999,999.99'), 'N/A'), 15));

        END LOOP;

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE('No data found for Tablespace Utilization by Datafile. Check privileges or data in DBA_DATA_FILES.');

        END IF;

        -- Section 3: Segment Level Utilization (Top 20 Largest Objects)

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- 3. Segment Level Utilization (Top 20 Largest Objects)                      --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('OWNER', 20) || RPAD('SEGMENT_NAME', 30) || 

                             RPAD('PARTITION_NAME', 30) || RPAD('SEGMENT_TYPE', 15) || 

                             RPAD('TABLESPACE_NAME', 25) || RPAD('Size(GB)', 15));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 20, '-') || ' ' || RPAD('-', 28, '-') || ' ' || 

                             RPAD('-', 28, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 23, '-') || ' ' || RPAD('-', 13, '-')); -- Total: 133 chars

        v_data_found := FALSE;

        FOR rec IN segment_util_cur LOOP

            v_data_found := TRUE;

            DBMS_OUTPUT.PUT_LINE(RPAD(rec.owner, 20) || 

                                 RPAD(rec.segment_name, 30) || 

                                 RPAD(NVL(rec.partition_name, 'NONE'), 30) || 

                                 RPAD(rec.segment_type, 15) || 

                                 RPAD(rec.tablespace_name, 25) || 

                                 RPAD(TO_CHAR(rec.size_gb, '999,999.99'), 15));

        END LOOP;

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE('No data found for Segment Level Utilization. Check privileges or data in DBA_SEGMENTS.');

        END IF;

        -- Section 4: Temporary Tablespace Utilization

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- 4. Temporary Tablespace Utilization (Using DBA_TEMP_FREE_SPACE)            --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('TABLESPACE_NAME', 25) || RPAD('Allocated(GB)', 15) || 

                             RPAD('Used(GB)', 15) || RPAD('Free(GB)', 15) || 

                             RPAD('Used Pct(%)', 12) || RPAD('Max Size(GB)', 15));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 13, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 10, '-') || ' ' || RPAD('-', 13, '-')); -- Total: 99 chars

        v_data_found := FALSE;

        FOR rec IN temp_tablespace_cur LOOP

            v_data_found := TRUE;

            DBMS_OUTPUT.PUT_LINE(RPAD(rec.tablespace_name, 25) || 

                                 RPAD(TO_CHAR(rec.allocated_size_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.used_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.free_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.used_pct, '999.99'), 12) || 

                                 RPAD(TO_CHAR(rec.max_size_gb, '999,999.99'), 15));

        END LOOP;

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE('No data found for Temporary Tablespace Utilization. Check privileges or data in DBA_TEMP_FREE_SPACE.');

        END IF;

        -- Section 5: Free Space Fragmentation

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- 5. Free Space Fragmentation by Tablespace                                  --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('TABLESPACE_NAME', 25) || RPAD('Fragments', 12) || 

                             RPAD('Largest Chunk(GB)', 18) || RPAD('Total Free(GB)', 15));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || ' ' || RPAD('-', 10, '-') || ' ' || 

                             RPAD('-', 16, '-') || ' ' || RPAD('-', 13, '-')); -- Total: 70 chars

        v_data_found := FALSE;

        FOR rec IN fragmentation_cur LOOP

            v_data_found := TRUE;

            DBMS_OUTPUT.PUT_LINE(RPAD(rec.tablespace_name, 25) || 

                                 RPAD(TO_CHAR(rec.fragments, '999,999'), 12) || 

                                 RPAD(TO_CHAR(rec.largest_chunk_gb, '999,999.99'), 18) || 

                                 RPAD(TO_CHAR(rec.total_free_gb, '999,999.99'), 15));

        END LOOP;

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE('No data found for Free Space Fragmentation. Check privileges or data in DBA_FREE_SPACE.');

        END IF;

        -- Section 6: Real-Time Temporary Tablespace Usage

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- 6. Real-Time Temporary Tablespace Usage (Using GV$TEMP_SPACE_HEADER)       --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE(RPAD('TABLESPACE_NAME', 25) || RPAD('Total Size(GB)', 15) || 

                             RPAD('Used(GB)', 15) || RPAD('Free(GB)', 15) || 

                             RPAD('Used Pct(%)', 12));

        DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 13, '-') || ' ' || RPAD('-', 13, '-') || ' ' || 

                             RPAD('-', 10, '-')); -- Total: 84 chars

        v_data_found := FALSE;

        FOR rec IN realtime_temp_cur LOOP

            v_data_found := TRUE;

            DBMS_OUTPUT.PUT_LINE(RPAD(rec.tablespace_name, 25) || 

                                 RPAD(TO_CHAR(rec.total_temp_size_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.used_temp_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.free_temp_space_gb, '999,999.99'), 15) || 

                                 RPAD(TO_CHAR(rec.used_pct, '999.99'), 12));

        END LOOP;

        IF NOT v_data_found THEN

            DBMS_OUTPUT.PUT_LINE('No data found for Real-Time Temporary Tablespace Usage. Check privileges or data in DBA_TEMP_FILES/GV$TEMP_SPACE_HEADER.');

        END IF;

        -- Final message

        DBMS_OUTPUT.PUT_LINE(' ');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE('-- Comprehensive Oracle Database Storage and Space Utilization Report completed. --');

        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');

        DBMS_OUTPUT.PUT_LINE(' ');

    EXCEPTION

        WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('Error during report generation: ' || SQLERRM);

            RAISE;

    END;

END;

/

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 ' '

Gem

 -- filename: rds_ash_awr_investigation.sql

--

-- Objective: To investigate heavy CPU hitters, session spikes, and top consumers

--            at a particular point in time using Oracle RDS AWR/ASH data.

--

-- Prerequisites:

--   - Oracle Diagnostics Pack License (Enterprise Edition).

--   - Connected to your Oracle RDS instance with sufficient privileges (e.g., rdsadmin user).

--   - Ensure SQL*Plus (or similar client) settings:

--     SET SERVEROUTPUT ON SIZE UNLIMITED

--     SET LONG 20000000 -- For full SQL text and AWR/ASH report output

--     SET PAGESIZE 0   -- No pagination

--     SET FEEDBACK OFF -- No "X rows selected" messages

--     SET HEADING OFF  -- No column headers (for report output)

--     SET TRIMSPOOL ON -- Trim trailing spaces from spool output

--

-- How to Use:

-- 1. Replace placeholder values for bind variables (e.g., :desired_timestamp, :begin_snap_id, :end_snap_id).

-- 2. (Optional but recommended for HTML reports) SPOOL the output to a .html file before executing the AWR/ASH report generation.

--    Example: SPOOL C:\temp\my_report.html

-- 3. Run the script: @rds_ash_awr_investigation.sql

-- 4. SPOOL OFF after execution.

-- 5. Open the .html file in a web browser for formatted reports.

----------------------------------------------------------------------------------------------------------------------------------------------------- -- 1. Define Bind Variables (ADJUST THESE VALUES) -------------------------------------------------------------------------------- -- 

--Declare bind variables using VAR command (client-side) 

VAR desired_timestamp TIMESTAMP; VAR ash_begin_time TIMESTAMP; VAR ash_end_time TIMESTAMP; VAR awr_begin_snap_id NUMBER; VAR awr_end_snap_id NUMBER; VAR top_n_count NUMBER; VAR generated_awr_filename VARCHAR2(256); -- New VAR for filename VAR generated_ash_filename VARCHAR2(256); -- New VAR for filename -- Execute all bind variable assignments within a single PL/SQL blockBEGIN :desired_timestamp := TO_TIMESTAMP('2025-07-09 00:30:00', 'YYYY-MM-DD HH24:MI:SS'); -- ADJUST ME! :ash_begin_time := :desired_timestamp - INTERVAL '5' MINUTE; -- ADJUST ASH WINDOW if needed:ash_end_time := :desired_timestamp + INTERVAL '5' MINUTE;  -- ADJUST ASH WINDOW if needed -- Find these snap IDs first using the "Find AWR Snapshots" query below. :awr_begin_snap_id := 12345; -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT) :awr_end_snap_id := 12346;  -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT) -- Define the N for TOP N queries :top_n_count := 10; DBMS_OUTPUT.PUT_LINE('-- Bind variables initialized.'); END; / -- PL/SQL block terminator -------------------------------------------------------------------------------- -- 2. Find AWR Snapshots (Run this first to get :awr_begin_snap_id and :awr_end_snap_id) -------------------------------------------------------------------------------- PROMPT -- Finding AWR Snapshots around the desired timestamp -- PROMPT -- Use these snap IDs for the :awr_begin_snap_id and :awr_end_snap_id variables above -- SET HEADING ON SET PAGESIZE 100 SELECTsnap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot WHERE begin_interval_time BETWEEN:desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Wider window to find snaps ORDER BY begin_interval_time; SET HEADING OFF SET PAGESIZE 0 PROMPT -- Adjust :awr_begin_snap_id and :awr_end_snap_id variables based on the output above -- PROMPT -- Then re-run the script from the top. -- --------------------------------------------------------------------------------

PROMPT -- Then re-run the script from the top. --

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-- 3. Generate AWR HTML Report (RDS-Adapted)

--------------------------------------------------------------------------------

PROMPT -- Generating AWR HTML Report... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

PROMPT -- Or you can try to read it directly below after generation (if it's not too large). --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    -- You cannot SELECT from it directly for output like a normal function.

    -- The output of this call will be the filename generated by RDS.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_awr_report for snaps ' || :awr_begin_snap_id || ' to ' || :awr_end_snap_id || '...');

    SELECT rdsadmin.rds_run_awr_report(

               l_begin_snap => :awr_begin_snap_id,

               l_end_snap   => :awr_end_snap_id,

               l_report_type => 'HTML'

           ) INTO :generated_awr_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('AWR Report generated on RDS: ' || :generated_awr_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating AWR report: ' || SQLERRM);

END;

/

PROMPT -- AWR Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR awr_filename VARCHAR2(256);

-- EXEC :awr_filename := :generated_awr_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read AWR report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :awr_filename));

-- PROMPT -- End of AWR Report Content. --

--

--------------------------------------------------------------------------------

-- 4. Generate ASH HTML Report (RDS-Adapted)

--------------------------------------------------------------------------------

PROMPT -- Generating ASH HTML Report for ASH window :ash_begin_time to :ash_end_time... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_ash_report for time ' || TO_CHAR(:ash_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(:ash_end_time, 'YYYY-MM-DD HH24:MI:SS') || '...');

    SELECT rdsadmin.rds_run_ash_report(

               begin_time => :ash_begin_time,

               end_time   => :ash_end_time,

               report_type => 'HTML'

           ) INTO :generated_ash_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('ASH Report generated on RDS: ' || :generated_ash_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating ASH report: ' || SQLERRM);

END;

/

PROMPT -- ASH Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR ash_filename VARCHAR2(256);

-- EXEC :ash_filename := :generated_ash_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read ASH report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :ash_filename));

-- PROMPT -- End of ASH Report Content. --

--

--------------------------------------------------------------------------------

-- 5. DB Load Profile (Minute-by-Minute Active Sessions)

--------------------------------------------------------------------------------

PROMPT -- DB Load Profile (Minute-by-Minute Active Sessions) around :desired_timestamp --

SET HEADING ON

SELECT

    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute_bucket,

    COUNT(*) AS total_active_sessions,

    COUNT(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE NULL END) AS on_cpu_sessions,

    COUNT(CASE WHEN session_state = 'WAITING' THEN 1 ELSE NULL END) AS waiting_sessions

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')

ORDER BY minute_bucket;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 6. Top N Sessions Consuming CPU

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Sessions Consuming CPU around :desired_timestamp --

SET HEADING ON

SELECT

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id,

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS "CPU_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

GROUP BY

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id

ORDER BY cpu_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 7. Top N SQL Statements by CPU Usage

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count SQL Statements by CPU Usage around :desired_timestamp --

SET HEADING ON

SELECT

    h.sql_id,

    TRUNC(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'ON CPU')) AS "CPU_Samples_%_of_Total_CPU",

    s.sql_text -- Note: SQL_TEXT is LONG, ensure SET LONG is adequate

FROM dba_hist_active_sess_history h

JOIN dba_hist_sqltext s ON h.sql_id = s.sql_id AND h.dbid = s.dbid

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

  AND h.sql_id IS NOT NULL

GROUP BY h.sql_id, s.sql_text

ORDER BY COUNT(*) DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 8. Top N Wait Events

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Wait Events around :desired_timestamp --

SET HEADING ON

SELECT

    h.event,

    h.wait_class,

    COUNT(*) AS wait_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'WAITING'), 2) AS "Wait_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'WAITING'

  AND h.wait_class != 'Idle'

GROUP BY h.event, h.wait_class

ORDER BY wait_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 9. Top N Users by Active Session Count

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Users by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    u.username,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY u.username

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 10. Top N Programs / Modules

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Programs and Modules by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    h.program,

    h.module,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY h.program, h.module

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 11. Top N Accessed Objects

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Accessed Objects by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    o.owner AS object_owner,

    o.object_name,

    o.object_type,

    COUNT(*) AS access_samples

FROM dba_hist_active_sess_history h

JOIN dba_objects o ON h.current_obj# = o.object_id AND h.dbid = o.owner_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.current_obj# IS NOT NULL

  AND o.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'AUDSYS', 'RDSADMIN')

  AND o.object_type IN ('TABLE', 'INDEX', 'PARTITION', 'SUBPARTITION')

GROUP BY o.owner, o.object_name, o.object_type

ORDER BY access_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 12. Total Connections History (from AWR DBA_HIST_SYSSTAT)

--------------------------------------------------------------------------------

PROMPT -- Total Connections History (Logons) around :desired_timestamp --

PROMPT -- (This shows logons per AWR snapshot interval, not live connections) --

SET HEADING ON

SELECT

    s.begin_interval_time,

    s.end_interval_time,

    stat.value AS total_logons_in_interval,

    ROUND(stat.value / EXTRACT(SECOND FROM (s.end_interval_time - s.begin_interval_time)), 2) AS logons_per_second

FROM dba_hist_sysstat stat

-- filename: rds_ash_awr_investigation.sql

--

-- Objective: To investigate heavy CPU hitters, session spikes, and top consumers

--            at a particular point in time using Oracle RDS AWR/ASH data.

--

-- Prerequisites:

--   - Oracle Diagnostics Pack License (Enterprise Edition).

--   - Connected to your Oracle RDS instance with sufficient privileges (e.g., rdsadmin user).

--   - Ensure SQL*Plus (or similar client) settings:

--     SET SERVEROUTPUT ON SIZE UNLIMITED

--     SET LONG 20000000 -- For full SQL text and AWR/ASH report output

--     SET PAGESIZE 0   -- No pagination

--     SET FEEDBACK OFF -- No "X rows selected" messages

--     SET HEADING OFF  -- No column headers (for report output)

--     SET TRIMSPOOL ON -- Trim trailing spaces from spool output

--

-- How to Use:

-- 1. Replace placeholder values for bind variables (e.g., :desired_timestamp, :begin_snap_id, :end_snap_id).

-- 2. (Optional but recommended for HTML reports) SPOOL the output to a .html file before executing the AWR/ASH report generation.

--    Example: SPOOL C:\temp\my_report.html

-- 3. Run the script: @rds_ash_awr_investigation.sql

-- 4. SPOOL OFF after execution.

-- 5. Open the .html file in a web browser for formatted reports.

--------------------------------------------------------------------------------

-- 1. Define Bind Variables (ADJUST THESE VALUES)

--------------------------------------------------------------------------------

-- Define your investigation timestamp (e.g., for yesterday 12:30 AM EST)

VAR desired_timestamp TIMESTAMP;

EXEC :desired_timestamp := TO_TIMESTAMP('2025-07-09 00:30:00', 'YYYY-MM-DD HH24:MI:SS'); -- ADJUST ME!

-- Define the time window for ASH-based queries (e.g., +/- 5 minutes around desired_timestamp)

VAR ash_begin_time TIMESTAMP;

EXEC :ash_begin_time := :desired_timestamp - INTERVAL '5' MINUTE; -- ADJUST ASH WINDOW if needed

VAR ash_end_time TIMESTAMP;

EXEC :ash_end_time := :desired_timestamp + INTERVAL '5' MINUTE; -- ADJUST ASH WINDOW if needed

-- Define the AWR snapshot IDs for AWR report generation

-- You need to find these first using the "Find AWR Snapshots" query below.

VAR awr_begin_snap_id NUMBER;

EXEC :awr_begin_snap_id := 12345; -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT)

VAR awr_end_snap_id NUMBER;

EXEC :awr_end_snap_id := 12346; -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT)

-- Define the N for TOP N queries

VAR top_n_count NUMBER;

EXEC :top_n_count := 10;

--------------------------------------------------------------------------------

-- 2. Find AWR Snapshots (Run this first to get :awr_begin_snap_id and :awr_end_snap_id)

--------------------------------------------------------------------------------

PROMPT -- Finding AWR Snapshots around the desired timestamp --

PROMPT -- Use these snap IDs for the :awr_begin_snap_id and :awr_end_snap_id variables above --

SET HEADING ON

SET PAGESIZE 100

SELECT snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

WHERE begin_interval_time BETWEEN :desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Wider window to find snaps

ORDER BY begin_interval_time;

SET HEADING OFF

SET PAGESIZE 0

PROMPT -- Adjust :awr_begin_snap_id and :awr_end_snap_id variables based on the output above --

PROMPT -- Then re-run the script from the top. --

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-- 3. Generate AWR HTML Report (RDS-Adapted)

--------------------------------------------------------------------------------

PROMPT -- Generating AWR HTML Report... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

PROMPT -- Or you can try to read it directly below after generation (if it's not too large). --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    -- You cannot SELECT from it directly for output like a normal function.

    -- The output of this call will be the filename generated by RDS.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_awr_report for snaps ' || :awr_begin_snap_id || ' to ' || :awr_end_snap_id || '...');

    SELECT rdsadmin.rds_run_awr_report(

               l_begin_snap => :awr_begin_snap_id,

               l_end_snap   => :awr_end_snap_id,

               l_report_type => 'HTML'

           ) INTO :generated_awr_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('AWR Report generated on RDS: ' || :generated_awr_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating AWR report: ' || SQLERRM);

END;

/

PROMPT -- AWR Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR awr_filename VARCHAR2(256);

-- EXEC :awr_filename := :generated_awr_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read AWR report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :awr_filename));

-- PROMPT -- End of AWR Report Content. --

--

--------------------------------------------------------------------------------

-- 4. Generate ASH HTML Report (RDS-Adapted)

--------------------------------------------------------------------------------

PROMPT -- Generating ASH HTML Report for ASH window :ash_begin_time to :ash_end_time... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_ash_report for time ' || TO_CHAR(:ash_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(:ash_end_time, 'YYYY-MM-DD HH24:MI:SS') || '...');

    SELECT rdsadmin.rds_run_ash_report(

               begin_time => :ash_begin_time,

               end_time   => :ash_end_time,

               report_type => 'HTML'

           ) INTO :generated_ash_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('ASH Report generated on RDS: ' || :generated_ash_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating ASH report: ' || SQLERRM);

END;

/

PROMPT -- ASH Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR ash_filename VARCHAR2(256);

-- EXEC :ash_filename := :generated_ash_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read ASH report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :ash_filename));

-- PROMPT -- End of ASH Report Content. --

--

--------------------------------------------------------------------------------

-- 5. DB Load Profile (Minute-by-Minute Active Sessions)

--------------------------------------------------------------------------------

PROMPT -- DB Load Profile (Minute-by-Minute Active Sessions) around :desired_timestamp --

SET HEADING ON

SELECT

    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute_bucket,

    COUNT(*) AS total_active_sessions,

    COUNT(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE NULL END) AS on_cpu_sessions,

    COUNT(CASE WHEN session_state = 'WAITING' THEN 1 ELSE NULL END) AS waiting_sessions

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')

ORDER BY minute_bucket;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 6. Top N Sessions Consuming CPU

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Sessions Consuming CPU around :desired_timestamp --

SET HEADING ON

SELECT

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id,

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS "CPU_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

GROUP BY

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id

ORDER BY cpu_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 7. Top N SQL Statements by CPU Usage

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count SQL Statements by CPU Usage around :desired_timestamp --

SET HEADING ON

SELECT

    h.sql_id,

    TRUNC(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'ON CPU')) AS "CPU_Samples_%_of_Total_CPU",

    s.sql_text -- Note: SQL_TEXT is LONG, ensure SET LONG is adequate

FROM dba_hist_active_sess_history h

JOIN dba_hist_sqltext s ON h.sql_id = s.sql_id AND h.dbid = s.dbid

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

  AND h.sql_id IS NOT NULL

GROUP BY h.sql_id, s.sql_text

ORDER BY COUNT(*) DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 8. Top N Wait Events

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Wait Events around :desired_timestamp --

SET HEADING ON

SELECT

    h.event,

    h.wait_class,

    COUNT(*) AS wait_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'WAITING'), 2) AS "Wait_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'WAITING'

  AND h.wait_class != 'Idle'

GROUP BY h.event, h.wait_class

ORDER BY wait_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 9. Top N Users by Active Session Count

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Users by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    u.username,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY u.username

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 10. Top N Programs / Modules

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Programs and Modules by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    h.program,

    h.module,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY h.program, h.module

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 11. Top N Accessed Objects

--------------------------------------------------------------------------------

PROMPT -- Top :top_n_count Accessed Objects by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    o.owner AS object_owner,

    o.object_name,

    o.object_type,

    COUNT(*) AS access_samples

FROM dba_hist_active_sess_history h

JOIN dba_objects o ON h.current_obj# = o.object_id AND h.dbid = o.owner_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.current_obj# IS NOT NULL

  AND o.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'AUDSYS', 'RDSADMIN')

  AND o.object_type IN ('TABLE', 'INDEX', 'PARTITION', 'SUBPARTITION')

GROUP BY o.owner, o.object_name, o.object_type

ORDER BY access_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

--------------------------------------------------------------------------------

-- 12. Total Connections History (from AWR DBA_HIST_SYSSTAT)

--------------------------------------------------------------------------------

PROMPT -- Total Connections History (Logons) around :desired_timestamp --

PROMPT -- (This shows logons per AWR snapshot interval, not live connections) --

SET HEADING ON

SELECT

    s.begin_interval_time,

    s.end_interval_time,

    stat.value AS total_logons_in_interval,

    ROUND(stat.value / EXTRACT(SECOND FROM (s.end_interval_time - s.begin_interval_time)), 2) AS logons_per_second

FROM dba_hist_sysstat stat

JOIN dba_hist_snapshot s ON stat.snap_id = s.snap_id AND stat.dbid = s.dbid AND stat.instance_number = s.instance_number

WHERE stat.stat_name = 'logons cumulative'

  AND s.begin_interval_time BETWEEN :desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Adjustable window

ORDER BY s.begin_interval_time;

SET HEADING OFF

PROMPT -- Investigation script execution complete. --

PROMPT -- Remember to review the generated AWR/ASH HTML reports from the AWS Console. --

-- Reset SQL*Plus settings (optional, good practice)

-- SET PAGESIZE 14

-- SET FEEDBACK ON

-- SET HEADING ON

-- SET TRIMSPOOL OFF

-- SET LONG 80JOIN dba_hist_snapshot s ON stat.snap_id = s.snap_id AND stat.dbid = s.dbid AND stat.instance_number = s.instance_number

WHERE stat.stat_name = 'logons cumulative'

  AND s.begin_interval_time BETWEEN :desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Adjustable window

ORDER BY s.begin_interval_time;

SET HEADING OFF

PROMPT -- Investigation script execution complete. --

PROMPT -- Remember to review the generated AWR/ASH HTML reports from the AWS Console. --

-- Reset SQL*Plus settings (optional, good practice)

-- SET PAGESIZE 14

-- SET FEEDBACK ON

-- SET HEADING ON

-- SET TRIMSPOOL OFF

-- SET LONG 80