Check Oracle DB Growth

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 SIZE”,t1.free “FREE SPACE”,(t1.free/t2.total)*100 “FREE (%)” ,(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_SIZE”,t1.free “FREE_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”,(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 : 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”,(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

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

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+ 24′, TRUE);
commit;
end;

Step: 11 View your dbms jobs and it’s other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth