| |
 |
|
Using v$datafile to track database growth
Oracle Tips by Burleson Consulting
June 14, 2010
|
Question: I am aware of using
dba_hist_seg_stat and STATSPACK extension table to track
database growth, put I hear that it is possible to do rudimentary
size tracking with the v$datafile view. Can I write a
script using v$datafile to see the database size changes?
Answer: I recommend
these approaches for creating Oracle database growth reports
and you can get size information from numerous sources:
- dba_hist_seg_stat
- v$datafile
-
dba_hist_tablespace_stat
- stats$tab_stats (custom created table)
In general, a production Oracle size report should have a
summary level and a drill-down so that you can see the growth of key
tables:
Database size change
Comparing the most recent snapshot dates
DB_NAME OLD_BYTES NEW_BYTES
CHANGE
--------- ------------- ----------------
----------------
prod 2,873,147,392 3,009,110,016
135,962,624
v$datafile queries for database size
Ameer Hameed, notes that you can use the
v$datafile.creation_time column to find out how many datafiles
were added in any given month.
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;
Stephane Faroult
offers this extended version of the v$datafile query to track
database size over time:
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
/
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2011 by Burleson Enterprises
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
|
|
|
