Raunak's DBA Blog

 -- ============================================================================ -- 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...

 -- ============================================================================ -- Oracle PDB Growth Analysis Script (Final Fixed Version) -- Author: <Your Name> -- Description: Tracks DB, Tablespace, and Segment growth in a PDB -- Safe for RAC/PDB environments. No SYS-only views. -- ============================================================================ SET LINES 200 SET PAGES 100 SET VERIFY OFF SET FEEDBACK OFF SET TRIMSPOOL ON -- Prompt for number of days ACCEPT num_days NUMBER PROMPT 'Enter number of days to analyze growth: ' -- Spool file with timestamp 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...

 -- ============================================================================ -- Oracle PDB Growth Analysis Script -- Author: <Your Name> -- Description: Tracks total DB, tablespace, and segment growth in a PDB -- Excludes SYSTEM, SYSAUX, UNDO, TEMP tablespaces and SYS/SYSTEM objects -- Safe for RAC/PDB environments -- ============================================================================ SET LINES 200 SET PAGES 100 SET VERIFY OFF SET FEEDBACK OFF SET TRIMSPOOL ON -- Prompt for number of days ACCEPT num_days NUMBER PROMPT 'Enter number of days to analyze growth: ' -- Generate spool filename 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 A...

 PROMPT PROMPT =============================================================== PROMPT SECTION 2: TABLESPACE-WISE GROWTH (Excludes SYSTEM/UNDO/TEMP) PROMPT =============================================================== COLUMN begin_time FORMAT A30 COLUMN end_time FORMAT A30 COLUMN tablespace_name FORMAT A30 COLUMN size_mb FORMAT 999,999,999 COLUMN used_mb FORMAT 999,999,999 COLUMN growth_mb FORMAT 999,999,999 SELECT     dt.tablespace_name,     TO_CHAR(s1.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') AS begin_time,     TO_CHAR(s2.end_interval_time, 'YYYY-MM-DD HH24:MI:SS')   AS end_time,     ROUND((e.tablespace_size * dt.block_size)/1024/1024)     AS size_mb,     ROUND((e.tablespace_usedsize * dt.block_size)/1024/1024) AS used_mb,     ROUND(((e.tablespace_usedsize - b.tablespace_usedsize) * dt.block_size)/1024/1024) AS growth_mb FROM     dba_hist_tbspc_space_usage b JOIN     dba_hist_tbspc...

 -- =========================================================================== -- Oracle PDB Database Growth Analysis Script -- Author: <Your Name> -- Description: Tracks DB, Tablespace, and Segment growth over N days -- Excludes SYSTEM, SYSAUX, UNDO, TEMP, SYS objects -- Run inside a PDB as DBA -- =========================================================================== SET LINESIZE 200 SET PAGESIZE 100 SET VERIFY OFF SET FEEDBACK ON SET TRIMSPOOL ON -- Prompt for number of days ACCEPT num_days NUMBER PROMPT 'Enter number of days to analyze growth: ' -- Spool file with timestamp 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 OVER &num_days DAYS PROMPT ===================================================== COLUMN begin_time FORMAT A30 COLUMN en...

 -- =========================================================================== -- 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 COLU...

 -- ============================================================================= -- Script Name : pdb_growth_report.sql -- Purpose     : PDB-safe, RAC-aware Database Growth Report (user input days) -- Author      : Raunak -- Sections    : [1] Total DB Growth, [2] Tablespace Growth (ID+Name), [3] Segment Growth -- Output      : Timestamped spool file -- ============================================================================= -- Prompt user for number of days ACCEPT days NUMBER PROMPT 'Enter number of days to analyze (e.g., 7, 30, 60): ' -- Create timestamp for spool file COLUMN dt NEW_VALUE file_time NOPRINT SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD_HH24MI') dt FROM dual; -- Begin spooling SPOOL db_growth_report_&&file_time..txt -- SQL*Plus 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 COL...

 -- ============================================================================= -- 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 ...

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

 -- ==================================================================================== -- 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: Run from SQL*Plus inside PDB with access to DBA_HIST_* views -- ==================================================================================== SET ECHO OFF SET LINESIZE 200 SET PAGESIZE 100 SET FEEDBACK OFF SET VERIFY OFF SET TRIMSPOOL ON -- === Accept user input for days === ACCEPT v_days NUMBER PROMPT 'Enter number of days to analyze growth (e.g., 30): ' -- === Generate spool file with timestamp === COLUMN spool_file_name NEW_VALUE spool_file SELECT 'db_growth_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.lst' AS spool_file_name FROM dual; SPOOL &spool_file -- === Column formatting === COLUMN START_TIME FORMAT A20 COLUMN END_TIME FORMAT A20 COLUMN TABLESPACE_NAME FORMAT A30 C...

-- ==================================================================================== -- Script Name : unused_objects_report.sql -- Purpose     : Identify unused or stale objects based on user-defined inactivity days -- Author      : ChatGPT for Raunak -- ==================================================================================== SET ECHO OFF SET LINESIZE 200 SET PAGESIZE 100 SET FEEDBACK OFF SET VERIFY OFF SET TRIMSPOOL ON -- === Accept user input for age threshold === ACCEPT v_days NUMBER PROMPT 'Enter number of days to consider an object as UNUSED (e.g., 90): ' -- === Generate timestamped spool file === COLUMN spool_file_name NEW_VALUE spool_file SELECT 'unused_objects_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.lst' AS spool_file_name FROM dual; SPOOL &spool_file PROMPT ============================================================================ PROMPT Report: Objects Unused for at least &v_days days PROMPT ====================...

 -- ============================================================================================ -- Script Name : sql_plan_flip_diagnose.sql -- Purpose     : Detect SQLs with Plan Flips, Diagnose Plan Performance, Manage Baselines -- Requirements: Must be run in SQL*Plus. Needs access to AWR views, DBA_SQL_PLAN_BASELINES -- ============================================================================================ SET ECHO OFF SET LINESIZE 300 SET PAGESIZE 100 SET VERIFY OFF SET FEEDBACK OFF SET TIMING OFF SET TRIMSPOOL ON SET LONG 1000000 SET LONGCHUNKSIZE 1000000 -- === Generate spool file with timestamp === COLUMN spool_file_name NEW_VALUE spool_file SELECT 'sql_plan_flip_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.lst' AS spool_file_name FROM dual; SPOOL &spool_file PROMPT ============================================================================ PROMPT [Step 1] Detect SQLs with Plan Flips in Last 7 Days PROMPT ====================================...

-- ============================================================================= -- 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      FORMA...