procedure for Calculating Database Growth and scheduling in DBMS JOBS....

1.

Create a Table By the Name db_growth...with following details...

Name Null? Type

----------------------------------------- -------- ----------------------------

DAY DATE

DATABASE_SIZE_MB NUMBER

DAILY_GROWTH_MB NUMBER

2.

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;

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(d

ay,'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;

3.Submit in DBMS_JOBS

variable jobno number;

begin

dbms_job.submit(

:jobno,

'database_growth ;',

trunc(sysdate+1) + 4/24,

'trunc(sysdate+1) + 4/24'

);

commit;

end;

/

print :jobno