Procedure to check Database growth on daily basis….

Procedure to check DB growth

1.Create a Table By the Name DB_GROWTH…with following details…

CREATE TABLE DB_GROWTH

( DAY DATE,

DATABASE_SIZE_GB NUMBER,

DAILY_GROWTH_GB 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;

/