Identified Every day Database Growth Size

Step 1: Create table

CREATE TABLE DB_GROWTH

(

DAY DATE,

DATABASE_SIZE_MB NUMBER,

DAILY_GROWTH_MB NUMBER

);

Step 2:

Create Store procedure , which generated automatic database growth size

CREATE OR REPLACE PROCEDURE database_growth

AS

today_size NUMBER;

yesterday_size NUMBER;

growth_size NUMBER;

cnt NUMBER;

BEGIN

SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED where tablespace_name <> 'UNDOTBS2';

SELECT COUNT(1) INTO cnt FROM db_growth ;

IF cnt > 0

THEN

SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(day,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');

ELSE

yesterday_size:=today_size;

END IF;

growth_size := today_size - yesterday_size;

INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);

EXCEPTION

WHEN no_data_found THEN

INSERT INTO db_growth VALUES(sysdate,today_size,0);

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Step 3:

Create DBMS database job, which one execute every day

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( job => X

,what => 'database_growth ;'

,next_date => to_date('26/04/2010 21:32:06','dd/mm/yyyy hh24:mi:ss')

,interval => 'SYSDATE+30/24'

,no_parse => TRUE

);

SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

END;

/

commit;

Note: if you needed are change job execution date and time manually

Step 4: Now see your CREATE TABLE DB_GROWTH, this (DAILY_GROWTH_MB) column are provide daily growth in database.