-

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

-- Script Name : pdb_db_growth_by_days.sql

-- Purpose     : Analyze Database Growth over Last N Days (User-Driven, PDB-aware)

-- Author      : ChatGPT (for Raunak)

-- Requirements: AWR enabled, read access to DBA_HIST_* views, executed in a PDB

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


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


SET LINESIZE 200

SET PAGESIZE 100

SET VERIFY OFF

SET FEEDBACK OFF

SET TRIMSPOOL ON


-- === Column Formatting ===

COLUMN START_TIME          FORMAT A20

COLUMN END_TIME            FORMAT A20

COLUMN TABLESPACE_NAME     FORMAT A30

COLUMN OWNER               FORMAT A20

COLUMN OBJECT_NAME         FORMAT A30

COLUMN SUBOBJECT_NAME      FORMAT A20

COLUMN OBJECT_TYPE         FORMAT A15

COLUMN GROWTH_MB           FORMAT 999,999,999.99

COLUMN GROWTH_GB           FORMAT 9999.99

COLUMN TOTAL_DB_GROWTH_GB  FORMAT 9999.99

COLUMN FIRST_SEEN          FORMAT A20

COLUMN LAST_SEEN           FORMAT A20

COLUMN INSTANCE_NUMBER     FORMAT 999


PROMPT

PROMPT ================================================================================

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

PROMPT ================================================================================


SELECT 

    MIN(s.begin_interval_time) AS START_TIME,

    MAX(s.end_interval_time)   AS END_TIME,

    ROUND(MAX(used_gb) - MIN(used_gb), 2) AS TOTAL_DB_GROWTH_GB

FROM (

    SELECT 

        ss.snap_id,

        SUM(tsu.tablespace_usedsize * ts.block_size) / 1024 / 1024 / 1024 AS used_gb

    FROM 

        dba_hist_tbspc_space_usage tsu

        JOIN dba_hist_snapshot ss ON tsu.snap_id = ss.snap_id AND tsu.dbid = ss.dbid

        JOIN dba_tablespaces ts ON tsu.tablespace_id = ts.ts#

    WHERE 

        ss.begin_interval_time >= SYSDATE - &days

    GROUP BY 

        ss.snap_id

) growth

JOIN dba_hist_snapshot s ON s.snap_id = growth.snap_id;


PROMPT

PROMPT ================================================================================

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

PROMPT ================================================================================


SELECT 

    ts.name AS TABLESPACE_NAME,

    MIN(ss.begin_interval_time) AS START_TIME,

    MAX(ss.end_interval_time) AS END_TIME,

    ROUND((MAX(tsu.tablespace_usedsize) - MIN(tsu.tablespace_usedsize)) * ts.block_size / 1024 / 1024 / 1024, 2) AS GROWTH_GB

FROM 

    dba_hist_tbspc_space_usage tsu

    JOIN dba_hist_snapshot ss ON tsu.snap_id = ss.snap_id AND tsu.dbid = ss.dbid

    JOIN dba_tablespaces ts ON ts.ts# = tsu.tablespace_id

WHERE 

    ss.begin_interval_time >= SYSDATE - &days

GROUP BY 

    ts.name

HAVING 

    ROUND((MAX(tsu.tablespace_usedsize) - MIN(tsu.tablespace_usedsize)) * ts.block_size / 1024 / 1024 / 1024, 2) > 0

ORDER BY 

    GROWTH_GB DESC;


PROMPT

PROMPT ================================================================================

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

PROMPT ================================================================================


SELECT 

    obj.owner,

    obj.object_name,

    obj.subobject_name,

    obj.object_type,

    ROUND(SUM(sss.space_used_delta) * 8 / 1024, 2) AS GROWTH_MB,

    MIN(s.begin_interval_time) AS FIRST_SEEN,

    MAX(s.end_interval_time) AS LAST_SEEN,

    MAX(s.instance_number) AS INSTANCE_NUMBER

FROM 

    dba_hist_seg_stat sss

    JOIN dba_hist_seg_stat_obj obj ON sss.obj# = obj.obj#

    JOIN dba_hist_snapshot s ON s.snap_id = sss.snap_id AND s.dbid = sss.dbid

WHERE 

    s.begin_interval_time >= SYSDATE - &days

GROUP BY 

    obj.owner, obj.object_name, obj.subobject_name, obj.object_type

HAVING 

    SUM(sss.space_used_delta) > 0

ORDER BY 

    GROWTH_MB DESC

FETCH FIRST 15 ROWS ONLY;


PROMPT

PROMPT === Script completed successfully for last &days days ===


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

-- END OF SCRIPT

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


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
-
 SET LINES 150 SET PAGES 1000 SET VERIFY OFF SET FEEDBACK OFF SET HEADING ON SET TRIMSPOOL ON SET LONG 10000 SET LONGCHUNKSIZE 10000 PROMPT =================================================== PROMPT       Oracle RAC Session Monitor - Full Version PROMPT =================================================== -- Step 1: Input Method PROMPT Choose input method: PROMPT Enter 'U' for Username or 'S' for SID/SERIAL# ACCEPT input_method CHAR DEFAULT 'U' -- Variables Initialization DEFINE sid = '' DEFINE serial = '' DEFINE instid = 1 DEFINE username = '' DEFINE sqlid = '' PROMPT -- Step 2: If Username mode, accept username and list sessions PROMPT Enter Username (leave blank if using SID/SERIAL#): ACCEPT username CHAR COLUMN inst_id_col NEW_VALUE instid COLUMN sid_col NEW_VALUE sid COLUMN serial_col NEW_VALUE serial SELECT inst_id, sid, serial#, status, program, module, sql_id, event FROM gv$session WHERE username = UPPER('&username...
Read more