Database Growth Monitoring

Step : 1 Calculate total Size of tablespace

select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files;


Step : 2 Calculate Free Space in Tablespace

select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space;

Step : 3 Calculate total size , free space and used space in tablespace

select t2.total "TOTAL DISK USAGE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)",(t2.total-t1.free) "USED SPACE", (1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2 ;


Step : 4 Create table which is store all free/use space related information of tablespace

create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_DISK_USAGE",t1.free "FREE_SPACE",(t2.total-t1.free) "USED_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);

Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)

insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t2.total-t1.free) "USED_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);

COMMIT;


Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)


create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t2.total-t1.free) "USED_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
;

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view


insert into db_growth select *
from v_db_growth;
COMMIT;


Step : 8 Check everything goes fine.

select * from db_growth;

Check Result

Step : 9 Execute following SQL for more time stamp information

alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.

Step : 10 Create a DBMS jobs which execute after 24 hours

declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 1', TRUE);
commit;
end;
/


PL/SQL procedure successfully completed.

Step: 11 View your dbms jobs and it's other information

select * from user_jobs;


-----If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically

exec dbms_job.run(ENTER_JOB_NUMBER)
exec dbms_job.run(23);



PL/SQL procedure successfully completed.

exec dbms_job.remove(21); ------to remove a job


Step: 12 Finally all data populated in db_growth table

select * from db_growth;