How long does Oracle retain the statistics for ?
The default period for which statistics are retained is 31 days but this can be altered with:-
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)
– where xx is the number of days to retain them
SQL>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 - will return the number of days stats are currently retained for. SQL>select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual; GET_STATS_HISTORY_AVAILABILITY 23/08/12 03:14:37.685878000 AM +04:00 - will return the oldest possible date stats can be restored from
How do I find the statistics history for a given table ?
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;SQL>select table_name, STATS_UPDATE_TIME from dba_tab_stats_history where owner='SCOTT' and table_name='EMP'; TABLE_NAME STATS_UPDATE_TIME EMP 23/09/12 11:44:30.445776 AM +04:00 Will show the times statistics were regathered for a given table.
Having decided what date you know the statistics were good for, you can use:-
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
ie execute dbms_stats.restore_table_stats ('SCOTT','EMP','20-SEP-12 11:44:30.445776 AM +04:00');