One of the simple way to find the growth of the database is using v$datafile view. following is the simple query and its output which gives the growth trend in month and year
</code> SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB 2 from v$datafile 3 group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM') 4 order by 1, 2; YEAR MO GB ---- -- ---------- 2004 10 14 2004 11 66 2004 12 47 2005 01 15 2005 02 14 2005 03 34 2005 04 14 2005 05 30 2005 06 5 2005 07 3 2005 08 9 YEAR MO GB ---- -- ---------- 2005 10 4 2005 12 20 2006 01 11 2006 02 15 2006 03 14 2006 04 16 2006 05 13 2006 06 0 2006 07 4 2006 08 17 2006 09 5 YEAR MO GB ---- -- ---------- 2006 10 6 2006 11 0 2006 12 30 2007 01 4 2007 02 4 2007 03 6 2007 04 6 2007 05 12 2007 06 15 2007 07 62 2007 08 29 YEAR MO GB ---- -- ---------- 2007 09 18 2007 11 20 2007 12 8 2008 03 8 2008 05 4 2008 08 11 2008 09 14 2008 10 4 2008 11 36 2008 12 49 2009 01 8 YEAR MO GB ---- -- ---------- 2009 02 38 2009 03 35 2009 04 54 2009 05 73 2009 08 1 2009 09 12 2009 10 78 2009 11 105 2009 12 113 2010 01 109 2010 02 79 YEAR MO GB ---- -- ---------- 2010 03 84 2010 05 243 2010 06 225 2010 07 244 2010 08 152 2010 09 40 2010 10 176 2010 11 76 2010 12 107 64 rows selected. <code>
Persistent Storage Solutions 
Hi Advait
I can see that that might well be an appropriate query for an Amazon database – assuming that you use fixed datafile sizes for ease of allocation/automation etc (and if you are that controlled I’d probably be recording growth in a management system elsewhere anyway :)). But in general that gives you the “current” size in GB of datafiles added in each of those months. As soon as you allow datafiles to autoextend then the result of that query isn’t really meaningful as a measure of monthly database growth.
Hi Niall, you are right. This works only if autoextend is off. Do you have any better suggestion for finding the exact growth trend of database.
OEM grid control has a Reports tab which gives the details about growth of the database.
Here are the steps to know Database growth pattern for last one month/year using OEM
1) Login to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for last one Month.
4) Also we can get one year Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and click on Oracle Database Tablespace Monthly Space Usage link.
Need to check how this can be done using a query
that’s nice really. Thnx
Without the cost of grid control, if one runs Statspack for a reasonable period of time ( I have 3 production years of it stashed away in a monitor instance), it’s very simple to extract that information from its tables. And a whole lot more.
Hi,
The information in this site very useful.
Noon – Could you share the scripts to monitor the database growth from statstpack/AWR please?
Regards,
Richard
Richard
You maye try running below query to get the overall DB growth (summarization of all the TBS growth) over the period of time (depending on your retention AWR snaps). I use it to monitor DB growth along with TBS
—
with
a as (
select a.name,a.ts# TBS_ID,
MIN(snap_id) Begin_snap_ID,
max(snap_id) End_Snap_ID,
min(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) begin_time,
max(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) End_time
from dba_hist_tbspc_space_usage,
(select name, ts#, block_size
from v$tablespace, dba_tablespaces
where name = tablespace_name) a
where tablespace_id = a.ts#
group by a.name, a.ts#,trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))
order by 4,5
),
a1 as (
select a.name,a.ts# TBS_ID,
MIN(snap_id) Begin_snap_ID,
max(snap_id) End_Snap_ID,
min(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) begin_time,
max(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) End_time
from dba_hist_tbspc_space_usage,
(select name, ts#, block_size
from v$tablespace, dba_tablespaces
where name = tablespace_name ) a
where tablespace_id = a.ts#
group by a.name,a.ts#, trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))
order by 4,5
),
b as (
select name,
TBS_ID,
min(a.Begin_snap_ID) Begin_Snap_ID_1,
max(a.End_Snap_ID) End_Snap_ID_2
from a
group by name, TBS_ID
),
c as (
select b.name,
b.TBS_ID,
b.Begin_Snap_ID_1 begin_snap_ID,
a.End_time Begin_time,
b.End_Snap_ID_2 End_snap_ID,
a1.End_time End_time
from a, b, a1
where b.Begin_Snap_ID_1 = a.Begin_snap_ID and
b.End_Snap_ID_2 = a1.End_snap_ID and a.name = b.name and
a1.name = b.name
),
d as (
select c.name,
dh.snap_id,
round((dh.tablespace_usedsize * 8192) / 1024 / 1024, 2) begin_Used_space
from dba_hist_tbspc_space_usage dh, c
where dh.snap_id = c.Begin_snap_ID and c.TBS_ID = dh.tablespace_id
),
e as (
select c.name,
dh.snap_id,
round((dh.tablespace_usedsize * 8192) / 1024 / 1024, 2) End_Used_space
from dba_hist_tbspc_space_usage dh, c
where dh.snap_id = c.End_Snap_ID and c.TBS_ID = dh.tablespace_id
)
select e.name,
to_char(c.begin_time, ‘DD-MON-YYYY’) Begin_time,
d.begin_Used_space,
to_char(c.End_time, ‘DD-MON-YYYY’) End_Time,
e.End_Used_space,
(e.End_Used_space – d.begin_Used_space) “Growth in MB”
from e, d, c
where e.name = d.name and e.name = c.name and d.snap_id = c.Begin_snap_ID and
e.snap_id = c.End_Snap_ID and
(e.End_Used_space – d.begin_Used_space) > 0
ORDER BY 1, 2 desc
—
i am trying to run this query on my database but it is giving alot of errors can u guide a way out for me.
anandmandilwar, exelent script to get this data 😀 thanks in advance