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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

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
- 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.

  to_char(CREATION_TIME,'RRRR') year, 
  to_char(CREATION_TIME,'MM') month, 
  sum(bytes) Bytes 
ee code depot for full script
group by 
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) / / d.block_size)) maxinc
from sys.file$ f,
     v$datafile d
where > 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)
             from (select f.file#,
                          f.create_blocks + x.rn * file_size,
                   from (select f.file#,
                                d.create_bytes / d.block_size create_blocks,
                                d.bytes / d.block_size blocks,
                         from sys.file$ f,
                              v$datafile d    
                         where > 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.blocks))
select "MONTH",
       round(cumul/1024, 2) GB,
       -- Draw a histogram
       rpad('=', round(60 * cumul / current_M), '=') volume
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,
      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')
                        -- 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,
                              from sys.seg$ s,
                                   extended_files f,
                         $ t,
                                   sys.obj$ o
                              where s.file# = f.file#
                                and s.type# = 5
                                and s.block# between f.prior_size and
                                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,
                              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
                                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(, 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