Tablespace Growth OEM (tablespace_growth_oem.sql)

What This Script Does

This comprehensive script analyzes tablespace growth trends using Oracle Enterprise Manager (OEM) metrics. It compares allocated and used space between two specific dates, calculates growth rates, and projects annual growth trends. Essential for capacity planning and monitoring database growth patterns.

The Script

set term off
SET HEADING ON
set space 1
set pagesize 62
set newpage  0
set linesize 140
set echo off
set verify off
set feedback off
ttitle off
clear columns
clear breaks
clear computes

alter session set nls_date_format = 'DD-MON-RR';

COLUMN start_date    PRINT new_value start_date  FORMAT DATE
COLUMN end_date   pRINT new_value end_date FORMAT DATE
column num_days    print new_value num_days  format 99,999

set term on

prompt
prompt *****************************************************************************************************
prompt *
prompt *  This script report tablespace growth between any two given collections for a specific instance
prompt *
prompt *****************************************************************************************************
prompt
accept db_name_in   prompt 'Enter instance name : '

 SELECT    m.rollup_timestamp TIMESTAMP,
           SUM (
              DECODE (m.metric_column, 'spaceAllocated', m.average/1024 , 0)
           )
              allocated_storage,
           SUM (DECODE (m.metric_column, 'spaceUsed', m.average/1024, 0))
              used_storage
    FROM   mgmt$metric_daily m, mgmt$target t
   WHERE   (t.target_type = 'rac_database'
            OR (t.target_type = 'oracle_database'
                AND t.type_qualifier3 != 'RACINST'))
           AND m.target_guid = t.target_guid
           AND m.metric_name = 'tbspAllocation'
           AND (m.metric_column = 'spaceAllocated'
                OR m.metric_column = 'spaceUsed')
           AND t.target_guid =
                 (SELECT   target_guid
                    FROM   mgmt$target t
                   WHERE   (t.target_type = 'rac_database'
                            OR (t.target_type = 'oracle_database'
                                AND t.type_qualifier3 != 'RACINST'))
                           AND UPPER(SUBSTR (
                                        t.target_name,
                                        1,
                                        DECODE (INSTR (t.target_name, '.') - 1,
                                                -1, LENGTH (t.target_name),
                                                INSTR (t.target_name, '.') - 1)
                                     )) = upper('&db_name_in.'))
GROUP BY   t.target_name, m.rollup_timestamp
ORDER BY   t.target_name, m.rollup_timestamp;

accept start_date  prompt 'Enter starting date (DD-MON-RR) : '
accept end_date prompt 'Enter ending date (DD-MON-RR)   : '

accept path_in     prompt 'Enter path for spool file : '

-- Additional detailed analysis with growth calculations follows...
-- [Script continues with comprehensive growth analysis and reporting]

Parameters

The script prompts for:

db_name_in: Database instance name to analyze
start_date: Starting date for analysis (DD-MON-RR format)
end_date: Ending date for analysis (DD-MON-RR format)
path_in: Directory path for output spool file

Usage

-- Basic usage (will prompt for all parameters)
@tablespace_growth_oem.sql

-- Example values when prompted:
-- Enter instance name: PRODDB
-- Enter starting date (DD-MON-RR): 01-JAN-25
-- Enter ending date (DD-MON-RR): 31-JAN-25
-- Enter path for spool file: /tmp/

Sample Output

The script generates both screen output and a detailed report file:

***************************************************************************
*
*  Tablespace Growth Trend Report  - PRODDB
*
*  Report Start Date  : 01-JAN-25
*  Report Ending Date : 31-JAN-25
*  Days Reporting     : 30
*
****************************************************************************

Instance         Tablespace Name           Start      Start    End        End      Meg Used   Meg Alloc
                                          Meg Alloc  Meg Used Meg Alloc  Meg Used  Change     Change
----------------- ------------------------- ---------- -------- ---------- -------- ---------- ----------
PRODDB           SYSTEM                         500      450        520      470         20         20
PRODDB           SYSAUX                         300      280        320      300         20         20
PRODDB           USERS                        1,000      800      1,200    1,000        200        200
PRODDB           DATA01                       5,000    4,200      6,000    5,100        900      1,000
PRODDB           INDEX01                      2,000    1,800      2,400    2,200        400        400
----------------- ------------------------- ---------- -------- ---------- -------- ---------- ----------
Totals :                                     8,800    7,530     10,440    9,070      1,540      1,640

The growth rate for last 30 days is 20%
The projected annual growth rate is 244%

To view the report go to file /tmp/PRODDB_ts_growth.txt