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.
