Dear All
I am little bit sceptical about using an way to actually measure database growth, we currently have 7 days retention period for AWR repository in our environment, and i think of using below PL/SQL block to measure the DB growth, but i came across few articles which says the data available from here can be only used as an estimation and not the exact figure and almost all says i need to go to traditional way of logging the statistics data via JOB or shell cronjob. Which is the best option ? any other way to do this activity ?
Option 1 (based on AWR repository) :
************
Declare
v_BaselineSize number(20);
v_CurrentSize number(20);
v_TotalGrowth number(20);
v_Space number(20);
cursor usageHist is
select a.snap_id,
SNAP_TIME,
sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
from
(select SNAP_ID,
sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
from DBA_HIST_SEG_STAT
group by SNAP_ID
having sum(SPACE_ALLOCATED_TOTAL) <> 0
order by 1 ) a,
(select distinct SNAP_ID,
to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
from DBA_HIST_SNAPSHOT ) b
where a.snap_id=b.snap_id;
Begin
select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
select sum(bytes) into v_CurrentSize from dba_segments;
v_BaselineSize := v_CurrentSize - v_TotalGrowth ;
dbms_output.put_line('SNAP_TIME Database Size(MB)');
for row in usageHist loop
v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
dbms_output.put_line(row.SNAP_TIME || ' ' || V_Space );
end loop;
end;
option 2 (traditional way) :
*******************************
--May be here i need to write scripts for weekly and monthly to enter the data in staging tables.
select (a.data_size+b.temp_size+c.redo_size)/1024/1024/1024 "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;