How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack).

Those tables could also be accessed manually.

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

To increase the retention, use DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings

4 Comments

Leave a Reply

Your email address will not be published.