Home > Scripts > Script: Object or Table Growth from AWR

Script: Object or Table Growth from AWR

Hello All,

Sometimes we were been asked to provide the object or a table growth, Here is some nice script, which is useful to find the table growth per day for a given table;

select   obj.owner, obj.object_name,
           to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
           sum(a.db_block_changes_delta) block_increase
  from     dba_hist_seg_stat a,
           dba_hist_snapshot sn,
           dba_objects obj
  where    sn.snap_id = a.snap_id
  and      obj.object_id = a.obj#
  and      obj.owner not in ('SYS','SYSTEM')
  and        obj.object_name='TEST_HIST'
  and      end_interval_time between to_timestamp('01-JAN-2012','DD-MON-RRRR')
           and to_timestamp('29-NOV-2012','DD-MON-RRRR')
  group by obj.owner, obj.object_name,
           to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
  order by obj.owner, obj.object_name
  /


OWNER            OBJECT_NAME        START_DAY   BLOCK_INCREASE
---------------- -------------  -----------     --------------
TEST             TEST_HIST      2012-NOV-22          18704
TEST             TEST_HIST      2012-NOV-23           9968
TEST             TEST_HIST      2012-NOV-26          10688
TEST             TEST_HIST      2012-NOV-27          10064
TEST             TEST_HIST      2012-NOV-28          10336
SQL> SQL>

The output shows the number of blocks that increased per day, you can calculate blocks & db_block_size and get the exact size if required.

Update: Another method as updated latest by Laurent in his blog post at http://laurentschneider.com/wordpress/2012/12/how-big-was-my-table-yesterday.html

SELECT savtime,owner,object_name,rowcnt,blkcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,
  dba_objects o
WHERE 
   o.owner='SCOTT'
   AND o.object_name='EMP'
   and o.object_id = W.OBJ#
ORDER BY o.owner, o.object_name, w.savtime;

 
SAVTIME           OWNER    OBJECT_NAME     ROWCNT     BLKCNT
----------------- -------- ----------- ---------- ----------
2012-11-06 06:49  SCOTT    EMP           13215425     120077
2012-11-13 07:28  SCOTT    EMP           12678535     120077
2012-11-20 03:15  SCOTT    EMP           12860640     120077
2012-11-27 03:19  SCOTT    EMP           13045850     120077
2012-12-04 05:41  SCOTT    EMP           13326460     120077

You can find other useful growth trend scripts here in this post:- http://sureshgandhi.wordpress.com/2012/10/15/scripts-databasetabletablespace-growth-report-using-awr/

-Hope this helps
Thanks
Suresh

About these ads
  1. Subrahmanya Sarma N
    December 23, 2012 at 10:13 pm | #1

    Nice queries

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

BigData - Oracle-info.com

Sureshgandhi's Blog : Its time to learn BigData

Oracle Alchemist

Steve Karam's Oracle Blog

ishudba

The King's .... "Joy of Life"..

the dba diary !!!

Naga's Blog : Oracle DBA experiences

ORACLE-INFO

Sureshgandhi's Blog: It's all about Oracle..Just Learn,Apply & Share.

SureshGandhi

All about Oracle.......

All about Oracle.......

DB Optimizer

database performance, SQL tuning and data visualizatoin

jarneil

The thoughts of Jason Arneil

All Things Oracle

All about Oracle.......

Dion Cho - Oracle Performance Storyteller

We are natural born scientists

All about Oracle.......

All about Oracle.......

All about Oracle.......

Miladin Modrakovic's Blog: Oraclue

Oracle internals, debugging and undocumented features

Coskan's Approach to Oracle

What I learned about Oracle

The Arup Nanda Blog

All about Oracle.......

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

All about Oracle.......

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

All about Oracle.......

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Iordan Iotzov's DBA blog

The opinions here may not necessarily reflect that of my employers - both past and present. The comments left by the reviewers are theirs alone and may not reflect my opinion. None of the advice is warranted to be free of errors and omissions. Please use at your own risk and after thorough testing in your environment.

Doug's Oracle Blog

All about Oracle.......

Oracle related stuff

All about Oracle.......

All about Oracle.......

Charles Hooper's Oracle Notes

Miscellaneous Random Oracle Topics: Stop, Think, ... Understand

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle E-Business Suite Technology

All about Oracle.......

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

Oracle DBA - Tips and Techniques

Knowledge Is Power - Share IT ....

Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Laurent Schneider

All about Oracle.......

Kerry Osborne's Oracle Blog

All about Oracle.......

Hemant's Oracle DBA Blog

All about Oracle.......

Oracle Enterprise Manager

All about Oracle.......

Structured Data

Thoughts on: Big Data, Hadoop, Databases, Platform, Performance & Scalability

Oracle Scratchpad

Just another Oracle weblog

Karl Arao's Blog

Just another weblog about Oracle,Linux,Troubleshooting,Performance,etc..etc..

The Data Warehouse Insider

All about Oracle.......

Online Apps DBA

One Stop Shop for Apps DBA's

All about Oracle.......

Microsoft SQL Server

All about Oracle.......

Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: