Keeping track of database space growth in production environments is a must-to-do job of every DBA. Maintaining and reporting information of historical database growth is always a task that involves good amount of planning and scheduling various jobs in the database.
Oracle provides time-series segment statistics for tables and index in the 10g Automated workload Repository tables (AWR). These can quickly extract data for Oracle growth reports. In Oracle 10g, total database growth reports are is easy because the dba_hist_seg_stat tables provides the delta value for every table and index. Starting from 10g, Oracle has provided us with a feature to achieve this without much overhead. Most importantly, we can use this with out incurring any extra license cost. This technique is used by many DBA’s but not much documentation is available over the internet for easy use.
This script is helpful in tracking the oracle growth
SQL> select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;
Output :
TABLESPACE_NAME CUR_USED_SIZE_MB AVG_INCREAS_MB
-------------------- ----------------------- ------------------------
ENOM_CM_DATA 1121.31 78.71
ENOM_CM_INDEX 2.38 0
ENOM_EL_CATALOG 4.13 .01
ENOM_EL_DATA 13.63 0
ENOM_EL_INDEX 16.06 .01
ENOM_GS_CATALOG 1 0
ENOM_GS_DATA 1 0
ENOM_LA_DATA 5 0
ENOM_LA_INDEX 3 0
EXAMPLE 78.44 0
SYSAUX 614.75 14.46
SYSTEM 692.88 1.32
UNDOTBS1 48 -.96
USERS 644.38 91.47
14 rows selected.
Enjoy :-)
AWESOME...just what I was looking for!!
ReplyDeleteThank you.
Hello Neeraj,
ReplyDeleteI am a Pl\sql developer and just starting to learn about DBA activities. I have been asked to track the weekly growth of one particular DB schema. Here sql is really helpful. Thankssssss a lot:)
Just curious..wanted to know if we can use SPACE_USED_DELTA in DBA_HIST_SEG_STAT to get the weekly growth. If yes, can you help me in writing that sql.
--Db Size Change
ReplyDelete/* Can be useful for identifing total db growth by analyzing all snapshots one by one , and calculating total size change over given period.*/
declare
--Cursor Declaretion
CURSOR c1
IS
select sum(round((tablespace_usedsize*8*1024)/1024/1024,2)) as "used space",snap_id from DBA_HIST_TBSPC_SPACE_USAGE
where snap_id in
(select snap_id
from dba_hist_snapshot
where to_char(BEGIN_INTERVAL_TIME) between '16/10/2011%18%' and '20/10/2011%18')
group by snap_id order by snap_id asc;
--Variable Declaretion
row_counter number;
db_used_size_1 number;
db_used_size_2 number;
snap_time timestamp;
total_change number;
begin
row_counter:=0;
total_change:=0;
db_used_size_2:=0;
db_used_size_1:=0;
FOR emp_rec IN c1
LOOP
row_counter:=row_counter+1;
if mod(row_counter,2)=0
then
db_used_size_2:=emp_rec."used space";
else
db_used_size_1:=emp_rec."used space";
end if;
if row_counter != 1
then
execute immediate 'select begin_interval_time from dba_hist_snapshot where snap_id ='||emp_rec.snap_id into snap_time;
if db_used_size_2-db_used_size_1 !=0 and db_used_size_1 !=0 and db_used_size_2!=0
then
if mod(row_counter,2)!=0
then
dbms_output.put_line(substr(to_char(snap_time),1,instr(snap_time,',')-1)||' -> '
||to_char(db_used_size_1-db_used_size_2)||'MB'||'--> %'||to_char(round(100*(db_used_size_1-db_used_size_2)/db_used_size_1,2)));
total_change:=total_change+(db_used_size_1-db_used_size_2);
else
dbms_output.put_line(substr(to_char(snap_time),1,instr(snap_time,',')-1)||' -> '||to_char(db_used_size_2-
db_used_size_1)||'MB'|| '--> %'||to_char(round(100*(db_used_size_2-db_used_size_1)/db_used_size_1,2)));
total_change:=total_change+(db_used_size_2-db_used_size_1);
end if;
end if;
end if;
END LOOP;
dbms_output.put_line('Total Change ' || to_char(total_change ||' MB' ));
end;
oh my god earslan your query amazing it is like finding water in desert after one year , thank you very much i can find anything i want ,my life is easier then before
ReplyDeletedefaultDBA
plsql above can be used with sysdate-"DAYS" with a little change in the following query.
ReplyDeleteEx:
(select snap_id
from dba_hist_snapshot
where to_char(BEGIN_INTERVAL_TIME) between '16/10/2011%18%' and '20/10/2011%18')
..change to..
(select snap_id
from dba_hist_snapshot
where BEGIN_INTERVAL_TIME > sysdate - 5 )
its just says..
ReplyDeletePL/SQl procedure sucessfully completed..
How to get the report..?
how to get the report?
ReplyDeleteIt just say:
PL/SQl procedure sucessfully completed..
@tom and @Anonymous said ...
ReplyDeletei think you have not set the serveroutput variable.
Check as below
SQL> show SERVEROUTPUT
serveroutput OFF (by default it is OFF)
SQL> set SERVEROUTPUT on
SQL> show SERVEROUTPUT
serveroutput ON
Now run the above scripts ...
Hope you got it and let me know if there is any doubt...
Good Luck :)
--Neeraj
Hi,
ReplyDeleteThanks for sharing query to check growth!
Looks like there is an issue with Earslan query ..
select sum(round
*********issue*********
((tablespace_usedsize*8*1024)/1024/1024,2))
**********Should Be******
((tablespace_usedsize*8)/1024/1024,2))
as "used space",snap_id from DBA_HIST_TBSPC_SPACE_USAGE
--------------------------
Also, why the Neeraj's query - sum of AVG_INCREASE_MB is not matching with Earslans query output for "Total Change MB"?
Thanks in advance!
Manish
Great Job!!I found this after a huge search. Thanks.
ReplyDeleteuseful script - you just need one more join: tsu.snap_id = ts.snap_id
ReplyDeleteThat pl/sql is not working for me .It gives me
ReplyDeleteError ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 36
I researched by commenting and uncommenting lines and got the error at
"execute immediate 'select begin_interval_time from dba_hist_snapshot where snap_id ='||emp_rec.snap_id into snap_time; "
because when I run cursor C1 in the database it returns a lot of rows .So I guess the error is obvious .
Hi Neeraj, the script provided by u is simply awesome.... i have a question....can u pls tel me that the that the groth rate shown by "AVG_INCREAS_MB" column is yearly, monthly or weekly? I will grateful to have ur reply.... Thanks lot for ur script :)
ReplyDeleteHi Earlson.... I tried ur script but the output is something lik below which is i am not able to understand....could u pls suggest me and help me out in this pls...
ReplyDelete-> -3.63MB--> %0
-> .19MB--> %0
-> 2.19MB--> %0
-> -11.19MB--> %0
-> 30.44MB--> %.01
-> -27.87MB--> %-.01
-> -.76MB--> %0
-> 2.38MB--> %0
-> -3.81MB--> %0
Total Change -9.43 MB
Regards,
Mukesh
Hi Earlson...
ReplyDeletei tried ur script and got below output which is i am not able to understand...could u pls suggest me and help me out in this pls...
-> -3.63MB--> %0
-> .19MB--> %0
-> 2.19MB--> %0
-> -11.19MB--> %0
-> 30.44MB--> %.01
-> -27.87MB--> %-.01
-> -.76MB--> %0
-> 2.38MB--> %0
-> -3.81MB--> %0
Total Change -9.43 MB
Regards,
Mukesh
Hi, the script provided by u guys is simply awesome.
ReplyDeleteWhen i run the script it gives me an output as below.
It will be grate help and highly appreciated if you could please let me understand what exactly the result says.
Thanks & Regards,
Syed
on giving the interval as
ReplyDeletewhere to_char(BEGIN_INTERVAL_TIME) between '01/01/2014%18%' and '31/01/2014%18')
---- to fix the characters length i paste half of the result.
-> -84.81MB--> %-.01
-> .13MB--> %0
-> -23MB--> %0
-> .19MB--> %0
-> 126.43MB--> %.02
-> 527.69MB--> %.07
-> 1621.56MB--> %.2
-> 113.06MB--> %.01
-> -463.88MB--> %-.06
-> 152.51MB--> %.02
-> 2989.43MB--> %.37
Total Change 392286.49 MB
Hi Neeraj,
ReplyDeleteDo i need to change anything if the database is running under ASM
Strength and growth come only through continuous effort and struggle. See the link below for more info.
ReplyDelete#growth
www.ufgop.org
hi.
ReplyDeleteThe query have too much cost. Perhaps is possible replace the dba_hist_tablespace_stat for v$tablespace
select b.name tablespace_name ,
MAX(b.used_size_mb) cur_used_size_mb ,
round(AVG(inc_used_size_mb),2)avg_increas_mb
from ( SELECT a.days,
a.name,
used_size_mb,
used_size_mb - LAG (used_size_mb,5) OVER ( PARTITION BY a.name ORDER BY a.name,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days,
ts.name ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_snapshot sp,
dba_hist_tbspc_space_usage tsu,
v$tablespace ts,
dba_tablespaces dt
where (sp.begin_interval_time > sysdate-30 and dt.contents='PERMANENT')
and tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.name = dt.tablespace_name
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
ts.name
ORDER BY ts.name, days ) a
) b GROUP BY b.name ORDER BY b.name
/
Regards
Hi Neeraj,
ReplyDeleteThanks for your post...it was really useful for me.
TABLESPACE_NAME CUR_USED_SIZE_MB AVG_INCREAS_MB
SYSAUX 9,486 168
SYSTEM 8,491 9
TBLSPC_ARCH 1,537 0
TBLSPC_DATA1 1,18,407 -4,408
TBLSPC_DATA2 3,259 0
TBLSPC_DATA3 31,475 0
TBLSPC_DSSDEV 3,708 0
TBLSPC_EVENT 3,719 0
TBLSPC_INDX1 15,194 16
UNDO1 7 0
UNDOTBS1 1,023 0
USERS 1 0
I have one doubt above output what is meaning of -4,408(negative values)...can u reply me pls...
Tried this but am getting output from one tablespace with null on the ave_increase
ReplyDelete