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 thoughts on “How big was my table yesterday

  1. Pingback: Script: Object or Table Growth from AWR « SureshGandhi

  2. Pingback: How big was my database last month | Laurent Schneider

  3. Laurent Schneider Post author

    Hi Matthias

    Alter the stats retention does not grant you the right to *access* those tables. I don’t know.

    In any doubt, ask your sales representative if they accept that you pay more 😉

    Cheers
    Laurent

Comments are closed.