DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, July 14, 2018

Rollback statistics



summary

Usually the database keeps a history of the statistics for one table for 31 days.

The following sql will return the number of days statistics are currently retained for.
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM dual;
The following sql will show the dates statistics were regathered for a given table.
SELECT TABLE_NAME, STATS_UPDATE_TIME
FROM dba_tab_stats_history
WHERE table_name LIKE 'TEST_ORDER%'
ORDER BY STATS_UPDATE_TIME DESC;
In case you want to rollback specific statistics in the past use the procedure.
BEGIN
DBMS_STATS.restore_table_stats('TESTSCHEMA','TEST_ORDER', TO_DATE('18/12/2018','dd/mm/rrrr'));
END;
/

No comments: