Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 


Using v$datafile to track database growth

Oracle Database Tips by Burleson Consulting

June 14, 2015

 
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:  Also see AWR solutions to table growth reports. and tracking row counts and block changes within tables.  Also we have the ability to track the growth of the whole schema and database  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

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 
ee code depot for full script
  v$datafile 
group by 
  to_char(CREATION_TIME,'RRRR'), 
  to_char(CREATION_TIME,'MM') 
order by 
  1, 2;

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
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
see code depot for full script
(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
/
For more complete Oracle sizing scripts, see the code depot download for full scripts
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

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.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster