Re: Database Growth for previous month
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Amir.Hameed@xxxxxxxxx
- Date: Fri, 04 Jun 2010 22:14:20 +0200
An extended version based on the same idea:
clear columns
set verify off
col tot_mon noprint new_value range
-- Compute how many months have gone since the database was created
select ceil(months_between(sysdate, created)) tot_mon
from v$database
/
col maxinc noprint new_value max_inc
-- Compute the maximum number of times a file created in 'autoextend' mode
-- has grown
select max(round((d.bytes - d.create_bytes) / f.inc / d.block_size)) maxinc
from sys.file$ f,
v$datafile d
where f.inc > 0
and f.file# = d.file#
and d.bytes > d.create_bytes
/
col GB format 9999990.00
col volume format A60
--
-- The factorized subquery tries to build a 'size history' for all
-- files that are in autoextend mode. It generates a list fo file#,
-- associated to 'prior size' and 'new size'. The snag is that a crucial
-- element is missing: the date when autoextension was triggered.
-- To fill the blanks as best as we can, we try to get the creation date
-- of the oldest data or index segment the segment header of which is
-- physically located in the new extension.
--
with extended_files as
(select file#,
nvl(lag(file_size, 1) over (partition by file#
order by file_size), 0)
prior_size,
file_size,
block_size
from (select f.file#,
f.create_blocks + x.rn * f.inc file_size,
f.block_size
from (select f.file#,
d.create_bytes / d.block_size create_blocks,
f.inc,
d.bytes / d.block_size blocks,
d.block_size
from sys.file$ f,
v$datafile d
where f.inc > 0
and f.file# = d.file#
and d.bytes > d.create_bytes
and rownum > 0) f,
(select rownum - 1 rn
from dual
connect by level <= &max_inc + 1) x
where (f.create_blocks + x.rn * f.inc) <= f.blocks))
select "MONTH",
round(cumul/1024, 2) GB,
-- Draw a histogram
rpad('=', round(60 * cumul / current_M), '=') volume
from (select to_char(cal.mon, 'MON-YYYY') "MONTH",
sum(nvl(evt.M, 0)) over (order by cal.mon range unbounded
preceding) cumul,
tot.curr_M current_M,
cal.mon
from -- current database size (data size)
(select round(sum(bytes)/1024/1024) curr_M
from v$datafile) tot,
-- all the months since the database was created
(select add_months(trunc(sysdate, 'MONTH'), -rn) mon
from (select rownum - 1 rn
from dual
connect by level <= &range)) cal,
-- all the months when the size of the database changed
(select size_date,
round(sum(bytes)/1024/1024) M
from (-- files in autoextend mode
select file#, max(bytes) bytes, size_date
from (select file#, bytes, trunc(min(ctime), 'MONTH')
size_date
-- Get the oldest creation date of tables or indexes
-- that are located in extensions.
-- Other segment types are ignored.
from (select s.file#,
f.file_size * f.block_size bytes,
o.ctime
from sys.seg$ s,
extended_files f,
sys.tab$ t,
sys.obj$ o
where s.file# = f.file#
and s.type# = 5
and s.block# between f.prior_size and
f.file_size
and s.file# = t.file#
and s.block# = t.block#
and t.obj# = o.obj#
union all
select s.file#,
f.file_size * f.block_size bytes,
o.ctime
from sys.seg$ s,
extended_files f,
sys.ind$ i,
sys.obj$ o
where s.file# = f.file#
and s.type# = 6
and s.block# between f.prior_size and
f.file_size
and s.file# = i.file#
and s.block# = i.block#
and i.obj# = o.obj#)
group by file#, bytes)
group by file#, size_date
union all
-- files that are not in autoextend mode
select d.file#,
d.create_bytes bytes,
trunc(d.creation_time, 'MONTH') size_date
from v$datafile d,
sys.file$ f
where nvl(f.inc, 0) = 0
and f.file# = d.file#)
group by size_date) evt
where evt.size_date (+) = cal.mon)
order by mon
/
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Hameed, Amir wrote:
>
> You can use the v$datafile.creation_time to find out how many
> datafiles were added in a month. I use the following statement:
>
>
>
> select
>
> to_char(CREATION_TIME,'RRRR') year,
>
> to_char(CREATION_TIME,'MM') month,
>
> sum(bytes) Bytes
>
> from
>
> v$datafile
>
> group by
>
> to_char(CREATION_TIME,'RRRR'),
>
> to_char(CREATION_TIME,'MM')
>
> order by
>
> 1, 2
>
>
>
> And it gives an o/p like below:
>
>
>
> _Year Month Growth_
>
> 2007 01 4,294,967,296
>
> 02 104,857,600
>
> 03 6,442,450,944
>
> 05 4,294,967,296
>
> 06 10,371,465,216
>
> 07 7,595,884,544
>
> 08 6,442,450,944
>
> 09 11,867,783,168
>
> 10 8,589,934,592
>
> 11 2,147,483,648
>
> 12 2,147,483,648
>
> ********** -------------------
>
> sum 64,299,728,896
>
>
>
> 2008 01 2,097,152,000
>
> 03 2,147,483,648
>
> 04 6,442,450,944
>
> 06 12,884,901,888
>
> 07 19,327,352,832
>
> 09 15,032,385,536
>
> 10 6,442,450,944
>
> 11 11,811,160,064
>
> 12 15,032,385,536
>
> ********** -------------------
>
> sum 91,217,723,392
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of
> *lyallbarbour@xxxxxxxxxxxxxxx
> *Sent:* Friday, June 04, 2010 3:05 PM
> *To:* oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: Database Growth for previous month
>
>
>
> We have Grid at our shop. Using the reports from it, that use the
> views mentioned in another reply, we have information since the Agents
> started reporting.
>
> Otherwise, previously, at other jobs, i've had to write scripts. Of
> course, if you do not have anything collecting information for the
> past two months, from Oracle or your own homegrown, then, you won't be
> able to get that information.
>
> Lyall
>
> -----Original Message-----
> From: Prabhu Krishnaswamy <prabhu_adam@xxxxxxxxxxx>
> To: oracle-l@xxxxxxxxxxxxx
> Sent: Fri, Jun 4, 2010 2:25 pm
> Subject: Database Growth for previous month
>
> Hi Lists,
>
> Could you please help me is there any way to get the database growth
> for the previous two months.
>
> Thanks in advance.
> Prabhu
>
> ------------------------------------------------------------------------
>
> Build a bright career through MSN Education Sign up now. =
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
