How to Automate the caculation of Database Growth and scheduling it in DBMS JOBS....

1.Create a Table By the Name db_growth as shown below...

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