Hello All,
I was given a requirement to restore stats of a table named scpomgr.PROCESSDFU as during the batch run stats for this table was deleted.
First check stats that are available for table in history table:
SQL> select * from dba_TAB_STATS_HISTORY where TABLE_NAME=’PROCESSDFU’ order by 5;
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
—————————— —————————— —————————— —————————— ——————————————————-
SCPOMGR PROCESSDFU 24-MAR-14 06.46.58.215534 PM -05:00
SCPOMGR PROCESSDFU 26-MAR-14 02.20.21.988521 PM -05:00
SCPOMGR PROCESSDFU 26-MAR-14 02.20.28.753781 PM -05:00
SCPOMGR PROCESSDFU 26-MAR-14 02.22.24.705528 PM -05:00
SCPOMGR PROCESSDFU 26-MAR-14 02.22.30.211436 PM -05:00
SCPOMGR PROCESSDFU 27-MAR-14 01.33.20.082902 AM -05:00
SCPOMGR PROCESSDFU 27-MAR-14 02.53.54.478502 AM -05:00
SCPOMGR PROCESSDFU 27-MAR-14 02.54.20.920565 AM -05:00
SCPOMGR PROCESSDFU 31-MAR-14 01.35.34.259835 AM -05:00
SCPOMGR PROCESSDFU 31-MAR-14 03.06.42.532219 AM -05:00
SCPOMGR PROCESSDFU 03-APR-14 01.34.34.239765 AM -05:00
SCPOMGR PROCESSDFU 05-APR-14 04.34.44.820826 PM -05:00
SCPOMGR PROCESSDFU 05-APR-14 04.34.50.505479 PM -05:00
SCPOMGR PROCESSDFU 07-APR-14 12.33.53.956840 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.16.58.848276 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.17.17.880748 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.18.28.036483 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.18.36.226104 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.49.09.915276 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.50.36.336024 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.51.08.913710 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 02.54.49.447963 AM -05:00
SCPOMGR PROCESSDFU 07-APR-14 03.09.02.222698 AM -05:00
SCPOMGR PROCESSDFU 10-APR-14 12.33.59.703584 AM -05:00
SCPOMGR PROCESSDFU 14-APR-14 08.05.56.219625 AM -05:00
SCPOMGR PROCESSDFU 14-APR-14 08.06.01.897461 AM -05:00
SCPOMGR PROCESSDFU 14-APR-14 12.19.01.910884 PM -05:00
SCPOMGR PROCESSDFU 17-APR-14 12.32.36.076777 AM -05:00
SCPOMGR PROCESSDFU 21-APR-14 06.19.38.103485 AM -05:00
SCPOMGR PROCESSDFU 21-APR-14 06.19.43.961863 AM -05:00
SCPOMGR PROCESSDFU 21-APR-14 06.20.42.277090 AM -05:00
SCPOMGR PROCESSDFU 21-APR-14 06.20.47.446610 AM -05:00
SCPOMGR PROCESSDFU 21-APR-14 06.22.41.857548 AM -05:00
SCPOMGR PROCESSDFU 21-APR-14 06.22.45.729694 AM -05:00
On querying the num_rows for scpomgr.PROCESSDFU we find the stats are missing:
SQL> select NUM_ROWS from dba_tab_statistics where TABLE_NAME=’PROCESSDFU’ and owner=’SCPOMGR’;
NUM_ROWS
———-
Then, upon deciding on the date for which stats are to be restored, run below command to restore the stats:
SQL> exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’,as_of_timestamp=>’05-APR-14 04.34.50.505479 PM -05:00′);
BEGIN DBMS_STATS.RESTORE_TABLE_STATS (ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’,as_of_timestamp=>’05-APR-14 04.34.50.505479 PM -05:00′); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 3668
ORA-06512: at “SYS.DBMS_STATS”, line 4501
ORA-06512: at “SYS.DBMS_STATS”, line 27802
ORA-06512: at line 1
Oops, the stats are locked for this table, so first you need to unlock the stats of this table:
SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS(ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’);
PL/SQL procedure successfully completed.
Then restore stats for 05th of April:
SQL> exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’,as_of_timestamp=>’05-APR-14 04.34.50.505479 PM -05:00′);
PL/SQL procedure successfully completed.
SQL> select NUM_ROWS from dba_tab_statistics where TABLE_NAME=’PROCESSDFU’ and owner=’SCPOMGR’;
NUM_ROWS
———-
643920
Done!
vjdba 