Database Growth Report

Scenario:

In our development, we need to generate the Database growth report after a majoy upgrade in our database. For example, if you are applying a big patch or loading a huge data into your database in development, then you should design a tool or script to generate this kind of database usage report.

Solution:

Design:

1. Store the database tablespace usage detailed information into database post upgrade. You might create a new user with a new table, says, user dbgrowth with table PRE_UPG_DB_GROWTH_STATS.

2. Upgrade

3. Store the database tablespace usage detailed information into database post upgrade with table name POST_UPG_DB_GROWTH_STATS 

4. Calcuate the delta between POST_UPG_DB_GROWTH_STATS – PRE_UPG_DB_GROWTH_STATS

5. Generate a friendly report.

Detailed script:

1. Create a user dbgrowth and grant it connect, resource, select_catalog_role,

CREATE USER dbgrowth identified by dbgrowth
DEFAULT TABLESPACE yourtablespace
TEMPORARY TABLESPACE <TEMP>
QUOTA UNLIMITED ON yourtablespace;

GRANT CONNECT, RESOURCE TO dbgrowth;
GRANT SELECT_CATALOG_ROLE TO dbgrowth;

2. Create two tables,

CREATE TABLE PRE_UPG_DB_GROWTH_STATS (TS_NAME VARCHAR2(240),
PRE_UPG_TOTAL_GB NUMBER(20,9),
PRE_UPG_USED_GB NUMBER(20,9),
PRE_UPG_FREE_GB NUMBER(20,9));

CREATE TABLE POST_UPG_DB_GROWTH_STATS (TS_NAME VARCHAR2(240),
POST_UPG_TOTAL_GB NUMBER(20,9),
POST_UPG_USED_GB NUMBER(20,9),
POST_UPG_FREE_GB NUMBER(20,9));

3. Insert tablespace stats. information into the Pre and Post tables respectively, for example, you can use below insert script to prepare the Pre upgrade information.

INSERT INTO PRE_UPG_DB_GROWTH_STATS (TS_NAME, PRE_UPG_TOTAL_GB,
PRE_UPG_USED_GB, PRE_UPG_FREE_GB)
VALUES (select a.tablespace_name, (sum(b.bytes)/count( distinct
a.file_id||'.'||a.block_id))/1024/1024/1024,
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024,
(sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024,
from dba_free_space a, dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name);

4. Generate a friendly report.

Here you can use Java (javax.xml.parsers.*, javax.xml.parser.* and javax.xml.transform.*) to write a dbgenerator to generate the report by combining your .xml data and your customized .xsl file.

5. If you are using Hudson, Cruise, then it is a good practice that plugin these two steps (pre, post) steps into your pipelines so that everyone can get the database growth reports in anytime and anywehere.