-

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

-- Oracle PDB Database Growth Analysis Script

-- Author: Raunak

-- Description: Tracks DB, tablespace, and segment growth in PDB (excludes SYS)

-- Run only inside a PDB

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


SET LINESIZE 200

SET PAGESIZE 100

SET VERIFY OFF

SET FEEDBACK ON

SET TRIMSPOOL ON


-- Prompt for days

ACCEPT num_days NUMBER PROMPT 'Enter number of days to analyze growth: '


-- Timestamped spool file

COLUMN spool_time NEW_VALUE SPOOLNAME

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS spool_time FROM dual;

SPOOL pdb_growth_report_&&spoolname..log


PROMPT

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

PROMPT SECTION 1: TOTAL DATABASE GROWTH (excluding SYSTEM stats)

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(sb.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') AS begin_time,

    TO_CHAR(se.end_interval_time, 'YYYY-MM-DD HH24:MI:SS')   AS end_time,

    ROUND((se.begin_value - sb.begin_value)/1024/1024)       AS growth_mb,

    ROUND(se.begin_value/1024/1024)                          AS db_size_mb

FROM (

    SELECT s.snap_id, s.dbid, s.instance_number, s.begin_interval_time, ss.value AS begin_value

    FROM   dba_hist_snapshot s

    JOIN   dba_hist_sysstat ss ON s.snap_id = ss.snap_id AND s.dbid = ss.dbid AND s.instance_number = ss.instance_number

    WHERE  ss.stat_name = 'db_block_changes'

      AND  s.begin_interval_time BETWEEN SYSDATE - &num_days AND SYSDATE

      AND  ROWNUM = 1

) sb,

(

    SELECT s.snap_id, s.dbid, s.instance_number, s.begin_interval_time, ss.value AS begin_value

    FROM   dba_hist_snapshot s

    JOIN   dba_hist_sysstat ss ON s.snap_id = ss.snap_id AND s.dbid = ss.dbid AND s.instance_number = ss.instance_number

    WHERE  ss.stat_name = 'db_block_changes'

      AND  s.begin_interval_time BETWEEN SYSDATE - &num_days AND SYSDATE

      AND  ROWNUM = 1

    ORDER BY s.snap_id DESC

) se;


PROMPT

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

PROMPT SECTION 2: TABLESPACE-WISE GROWTH (Excludes SYSTEM/SYSAUX/UNDO/TEMP)

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


COLUMN begin_time FORMAT A30

COLUMN end_time FORMAT A30

COLUMN tablespace_id FORMAT 99999

COLUMN tablespace_name FORMAT A25

COLUMN size_mb FORMAT 999,999,999

COLUMN used_mb FORMAT 999,999,999

COLUMN growth_mb FORMAT 999,999,999


SELECT

    ts.ts#                            AS tablespace_id,

    ts.name                           AS tablespace_name,

    TO_CHAR(sb.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') AS begin_time,

    TO_CHAR(se.end_interval_time, 'YYYY-MM-DD HH24:MI:SS')   AS end_time,

    ROUND((E.tablespace_size * ts.block_size)/1024/1024) AS size_mb,

    ROUND((E.tablespace_usedsize * ts.block_size)/1024/1024) AS used_mb,

    ROUND(((E.tablespace_usedsize - B.tablespace_usedsize) * ts.block_size)/1024/1024) AS growth_mb

FROM

    dba_hist_tbspc_space_usage B,

    dba_hist_tbspc_space_usage E,

    dba_hist_snapshot sb,

    dba_hist_snapshot se,

    ts$ ts

WHERE

    B.snap_id = sb.snap_id

    AND E.snap_id = se.snap_id

    AND sb.dbid = se.dbid

    AND sb.instance_number = se.instance_number

    AND B.tablespace_id = E.tablespace_id

    AND B.tablespace_id = ts.ts#

    AND B.dbid = E.dbid

    AND B.instance_number = E.instance_number

    AND sb.begin_interval_time BETWEEN SYSDATE - &num_days AND SYSDATE

    AND B.snap_id < E.snap_id

    AND ts.name NOT IN ('SYSTEM','SYSAUX')

    AND ts.name NOT LIKE 'UNDO%'

    AND ts.name NOT LIKE 'TEMP%'

    AND ROWNUM = 1

ORDER BY

    ts.name;


PROMPT

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

PROMPT SECTION 3: SEGMENT-WISE GROWTH (Excludes SYS/SYSTEM tablespaces)

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


COLUMN owner FORMAT A20

COLUMN segment_name FORMAT A30

COLUMN segment_type FORMAT A15

COLUMN tablespace_name FORMAT A20

COLUMN growth_mb FORMAT 999,999,999


SELECT

    owner,

    segment_name,

    segment_type,

    tablespace_name,

    ROUND(SUM(bytes)/1024/1024, 2) AS growth_mb

FROM (

    SELECT

        s1.owner,

        s1.segment_name,

        s1.segment_type,

        s1.tablespace_name,

        (s2.bytes - s1.bytes) AS bytes

    FROM

        (SELECT * FROM dba_hist_seg_stat s

         JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id

         WHERE sn.begin_interval_time BETWEEN SYSDATE - &num_days AND SYSDATE

           AND s.tablespace_id NOT IN (

               SELECT ts# FROM ts$ WHERE name IN ('SYSTEM','SYSAUX') OR name LIKE 'UNDO%' OR name LIKE 'TEMP%'

           )

        ) s1,

        (SELECT * FROM dba_hist_seg_stat s

         JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id

         WHERE sn.begin_interval_time BETWEEN SYSDATE - &num_days AND SYSDATE

        ) s2

    WHERE

        s1.owner = s2.owner

        AND s1.segment_name = s2.segment_name

        AND s1.snap_id < s2.snap_id

        AND s1.obj# = s2.obj#

)

WHERE bytes > 0

GROUP BY owner, segment_name, segment_type, tablespace_name

ORDER BY growth_mb DESC;


SPOOL OFF

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