Question:
I want to be able to track the growth of my
table from day to day and I understand that the
wri$_optstat_tab_history table can be used to track the
table size over time .
What is a query that uses
wri$_optstat_tab_history to track table size?
Answer:
Yes, wri$_optstat_tab_history is
only one of several ways to track table growth. 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
and
database growth reports.
select
savtime,
owner,
object_name,
rowcnt,
blkcnt
from
sys.wri$_optstat_tab_history w,
dba_objects
o
where
see code depot download for full scripts
o.owner='SCOTT'
and
o.object_name='MYTAB'
and
o.object_id = w.obj#
order by
o.owner,
o.object_name,
w.savtime;
Here is the table size output, showing the changes in the
number of rows and the number of data blocks assigned to the
table.
SAVTIME
OWNER
OBJECT_NAME
ROWCNT
BLKCNT
----------------- --------
----------- ---------- ----------
2012-11-06 06:49
SCOTT
MYTAB
13215425
120077
2012-11-13 07:28
SCOTT
MYTAB
12678535
120077
2012-11-20 03:15
SCOTT
MYTAB
12860640
120077
2012-11-27 03:19
SCOTT
MYTAB
13045850
120077
2012-12-04 05:41
SCOTT
MYTAB
13326460
120077