Friday, March 23, 2012

How many rows i had in my table?? History of row count

This question often comes to developer/DBA's mind that how many rows a table had in past. These type of information is quite handy when explain plan change happens. As we know change in data volume can also change the explain plan.
So keeping the history and knowing table growth trend one can know when a particular plan can change.

Whenever stats are gathered on table, its old stats like rowcount,numbers of blocks, average row len etc are flushed to SYS.WRI$_OPTSTAT_TAB_HISTORY table.
So its bit easy to get the history of rowcount for any table.

Like
SQL> select n.object_name as T,o.ROWCNT,o.BLKCNT,to_char(o.SAVTIME,'hh/dd/mm HH:
MI') as time from dba_objects n,WRI$_OPTSTAT_TAB_HISTORY o where o.obj#=n.object
_id and n.object_name='T1';

Table    ROWCNT     BLKCNT TIME
---------- ---------- --------------
T1
              4000        155 12/23/03 12:13

T1
              4000        155 12/23/03 12:15

T1
                                     10/29/02 10:01

Needless to say this view can have zero rows if stats where on gathered at all on table.

Enjoy!

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I am using this statement but its now working.
    Please guide how to use this i am new to SQL and oracle.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete