-

 -- =============================================================================

-- Script Name : pdb_growth_report.sql

-- Purpose     : PDB-safe Database Growth Report (User-defined day range)

-- Author      :  Raunak

-- Sections    : [1] Total DB Growth, [2] Tablespace Growth, [3] Segment Growth

-- Output      : Timestamped spool file (e.g., db_growth_report_2025_06_30_1930.txt)

-- =============================================================================


-- === Prompt for number of days ===

ACCEPT days NUMBER PROMPT 'Enter number of days to analyze (e.g., 7, 30, 60): '


-- === Create timestamped spool file ===

COLUMN dt NEW_VALUE file_time NOPRINT

SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD_HH24MI') dt FROM dual;


SPOOL db_growth_report_&&file_time..txt


-- === SQL*Plus display formatting ===

SET LINESIZE 200

SET PAGESIZE 100

SET FEEDBACK OFF

SET TRIMSPOOL ON


-- === Column formatting ===

COLUMN start_time FORMAT A30

COLUMN end_time FORMAT A30

COLUMN first_seen FORMAT A30

COLUMN last_seen FORMAT A30

COLUMN total_db_growth_gb FORMAT 9999.99

COLUMN tablespace_id FORMAT 999

COLUMN tablespace_name FORMAT A25

COLUMN growth_gb FORMAT 9999.99

COLUMN owner FORMAT A15

COLUMN object_name FORMAT A25

COLUMN subobject_name FORMAT A20

COLUMN object_type FORMAT A15

COLUMN growth_mb FORMAT 999999.99

COLUMN instance_number FORMAT 999


-- =============================================================================

PROMPT

PROMPT === [1] Total Database Growth Over Last &days Days ===

-- =============================================================================


SELECT 

    TO_CHAR(MIN(snap.begin_interval_time), 'DD-MON-YY HH24:MI') AS start_time,

    TO_CHAR(MAX(snap.end_interval_time),   'DD-MON-YY HH24:MI') AS end_time,

    ROUND(MAX(x.used_gb) - MIN(x.used_gb), 2) AS total_db_growth_gb

FROM (

    SELECT 

        tsu.snap_id,

        SUM(tsu.tablespace_usedsize) * 8192 / 1024 / 1024 / 1024 AS used_gb

    FROM 

        dba_hist_tbspc_space_usage tsu

        JOIN dba_hist_snapshot snap ON tsu.snap_id = snap.snap_id

    WHERE 

        snap.begin_interval_time >= SYSDATE - &days

    GROUP BY 

        tsu.snap_id

) x

JOIN dba_hist_snapshot snap ON snap.snap_id = x.snap_id;


-- =============================================================================

PROMPT

PROMPT === [2] Tablespace-wise Growth Over Last &days Days ===

-- =============================================================================


SELECT 

    tsu.tablespace_id,

    TO_CHAR(MIN(snap.begin_interval_time), 'DD-MON-YY HH24:MI') AS start_time,

    TO_CHAR(MAX(snap.end_interval_time),   'DD-MON-YY HH24:MI') AS end_time,

    ROUND((MAX(tsu.tablespace_usedsize) - MIN(tsu.tablespace_usedsize)) * 8192 / 1024 / 1024 / 1024, 2) AS growth_gb

FROM 

    dba_hist_tbspc_space_usage tsu

    JOIN dba_hist_snapshot snap ON tsu.snap_id = snap.snap_id

WHERE 

    snap.begin_interval_time >= SYSDATE - &days

GROUP BY 

    tsu.tablespace_id

HAVING 

    (MAX(tsu.tablespace_usedsize) - MIN(tsu.tablespace_usedsize)) > 0

ORDER BY 

    growth_gb DESC;


-- =============================================================================

PROMPT

PROMPT === [3] Top Growing Segments Over Last &days Days ===

-- =============================================================================


SELECT 

    obj.owner,

    obj.object_name,

    obj.subobject_name,

    obj.object_type,

    ROUND(SUM(ss.space_used_delta) * 8 / 1024, 2) AS growth_mb,

    TO_CHAR(MIN(snap.begin_interval_time), 'DD-MON-YY HH24:MI') AS first_seen,

    TO_CHAR(MAX(snap.end_interval_time),   'DD-MON-YY HH24:MI') AS last_seen,

    snap.instance_number

FROM 

    dba_hist_seg_stat ss

    JOIN dba_hist_seg_stat_obj obj ON ss.obj# = obj.obj#

    JOIN dba_hist_snapshot snap ON ss.snap_id = snap.snap_id

WHERE 

    snap.begin_interval_time >= SYSDATE - &days

GROUP BY 

    obj.owner, obj.object_name, obj.subobject_name, obj.object_type, snap.instance_number

HAVING 

    SUM(ss.space_used_delta) > 0

ORDER BY 

    growth_mb DESC

FETCH FIRST 15 ROWS ONLY;


-- === End spool ===

SPOOL OFF

PROMPT

PROMPT  Report successfully written to: db_growth_report_&&file_time..txt

Popular posts from this blog

-
Image
How to shutdown a Single Instance Database in Oracle 19c Overview Shutdown is an operation performed when a command is issued by the Administrator to bring down the Database or when it receives a signal to bring down the Database incase of a failure/malfunction or due to SHUTDOWN ABORT comm and execution. The options used by t he Administrator to bring down the Database is called as Shutdown Modes and the Database Server follows a sequence to shutdown a Database . Let's check how does that happen. Concepts The following are the shutdown Modes. Shutdown Modes A database administrator with SYSDBA or SYSOPER privileges can shut down the database using the SQL*Plus SHUTDOWN command or Enterprise Manager. The SHUTDOWN command has the following options that determine the shutdown behavior. Now let's look into each SHUTDOWN option in detail. SHUTDOWN ABORT  This mode is used for emergency situations, such as when shutdown with "IMMEDIATE" option doesn't work. This mode o...
Read more
-
How to startup a Single Instance Database in Oracle 19c Overview An Oracle Database goes through various stages until it is made available to the users. They are categorized into the below stages. Concepts The following are the stages of the startup sequence. Nomount   The instance is started, but is not yet associated with a database. The stages are as follows:  1. Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)  2. Reads the parameter file to determine the values of initialization parameters  3. Allocates the SGA based on the initialization parameter settings  4. Starts the Oracle background processes  5. Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax At this stage, no database is associated with the instanc...
Read more
-
 -- ============================================================================ -- Oracle PDB Growth Analysis Script (Final, Verified) -- Author: <Your Name> -- Works inside PDB, no SYS-only views, fully accessible -- ============================================================================ SET LINES 200 SET PAGES 100 SET VERIFY OFF SET FEEDBACK OFF SET TRIMSPOOL ON ACCEPT num_days NUMBER PROMPT 'Enter number of days to analyze growth: ' COLUMN spool_time NEW_VALUE spoolname SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') spool_time FROM dual; SPOOL pdb_growth_report_&&spoolname..log -- ============================================================================ PROMPT PROMPT =========================== PROMPT SECTION 1: TOTAL DB GROWTH PROMPT =========================== COLUMN begin_time FORMAT A30 COLUMN end_time FORMAT A30 COLUMN db_size_mb FORMAT 999,999,999 COLUMN growth_mb FORMAT 999,999,999 SELECT     TO_CHAR(MIN(s.begin_interval_time), 'YYYY-M...
Read more