How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history.

Without catalog :


select to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
from 
(
  select max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from v$backup_datafile 
  group by FILE# ,trunc(completion_time,'IW')
) 
group by d 
order by d;


COMPL        GB
-------- ------
W30-2012   3.73
W31-2012   4.84
W32-2012   5.00
W33-2012   5.05
W34-2012   5.35
W35-2012   5.80
W36-2012   6.12
W37-2012   6.39
W38-2012    .93
W39-2012   7.02
W40-2012   7.56
W41-2012   7.72
W42-2012   7.88
W43-2012   8.08
W44-2012   8.83
W45-2012   9.03
W46-2012   9.45
W47-2012   9.61
W48-2012  10.11
W49-2012  10.29
W50-2012  10.38

The history mostly depends on control_file_record_keep_time. If you do not use an rman catalog, set it to a high value like 93 (3M) or 366 (1Y)

With the rman catalog, use the RC_ view


select DB_NAME,to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
from 
(
  select DB_NAME,max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from rc_backup_datafile 
  group by DB_NAME,FILE# ,trunc(completion_time,'IW')
) 
group by DB_NAME,d 
order by DB_NAME,d;


DB_NAME  COMPL        GB
-------- -------- ------
DB01     W30-2012   3.73
DB01     W31-2012   4.83
DB01     W32-2012   5.00
DB01     W33-2012   5.05
DB01     W34-2012   5.34
DB01     W35-2012   5.79
DB01     W36-2012   6.11
DB01     W37-2012   6.39
DB01     W38-2012    .93
DB01     W39-2012   7.01
DB01     W40-2012   7.56
DB01     W41-2012   7.71
DB01     W42-2012   7.87
DB01     W43-2012   8.08
DB01     W44-2012   8.82
DB01     W45-2012   9.02
DB01     W46-2012   9.44
DB01     W47-2012   9.60
DB01     W48-2012  10.10
DB01     W49-2012  10.28
DB01     W50-2012  10.37

If you need to check which table grows the most, check How big was my table yesterday. But remember, RMAN backup is free to use, AWR and the WRI$ tables require the diagnostic pack and the Enterprise edition

4 thoughts on “How big was my database last month”

  1. Nice and useful idea. I would extend it to cover tablespace block size (not just the default one). Example (hopefully the correct extension) is below. Thanks.

    select DB_NAME,to_char(d,'”W”IW-IYYY’) compl, sum(b)/1024/1024/1024 GB
    from
    (
    select DB_NAME,max(DATAFILE_BLOCKS*BLOCK_SIZE) b, trunc(completion_time,’IW’) d
    from rc_backup_datafile where DB_NAME=’&DB_NAME’
    group by DB_NAME,FILE# ,trunc(completion_time,’IW’)
    )
    group by DB_NAME,d
    order by 1;

  2. We are often asked not for size of the database itself but rather for size of the real backupsets on the tapes. So far we used db view v$rman_backup_job_details to get this information. But with small modification of Laurent’s idea we can get the same information directly from rman catlog as well. That is maybe more practical.
    Example is below:

    select DB_NAME,to_char(d,'”W”WW-IYYY’) compl, sum(b)/1024/1024/1024 GB
    from
    (
    select DB_NAME,max(BLOCKS*BLOCK_SIZE) b, trunc(completion_time,’WW’) d
    from rc_backup_datafile where DB_NAME=’&DB_NAME’
    group by DB_NAME,FILE# ,trunc(completion_time,’WW’)
    )
    group by DB_NAME,d
    order by 1;

    One note: I replaced ISO format by calendar one as well.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>