1) Check last analyzed date of scott schema tables
SQL> select table_name, last_analyzed from DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME LAST_ANAL
------------------------------ ---------
TEMP 18-AUG-13
SALGRADE 18-AUG-13
BONUS 18-AUG-13
EMP 18-AUG-13
DEPT 18-AUG-13
2) Create stat table in users Tablespace
SQL> exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'stats_bkp_scott', tblspace => 'USERS');
PL/SQL procedure successfully completed.
3) Take the statistics backup of scott schema in stat table
SQL> exec dbms_stats.export_schema_stats(ownname => 'SCOTT', stattab => 'stats_bkp_scott');
PL/SQL procedure successfully completed.
4) Gather the statistics
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'EMP', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.
5) Check the last analyzed date of tables
SQL> select table_name, last_analyzed from DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME LAST_ANAL
------------------------------ ---------
STATS_BKP_SCOTT
DEPT 23-AUG-13
EMP 23-AUG-13
BONUS 18-AUG-13
SALGRADE 18-AUG-13
TEMP 18-AUG-13
6 rows selected.
6) Import/Revert the statistics of one/two table from the backup
SQL> exec dbms_stats.import_table_stats(ownname=>'SCOTT', tabname=>'EMP', statown=>'SCOTT', stattab=>'stats_bkp_scott', cascade=>true);
PL/SQL procedure successfully completed.
7) Check the last analyzed date of the tables
SQL> select table_name, last_analyzed from DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME LAST_ANAL
------------------------------ ---------
STATS_BKP_SCOTT
TEMP 18-AUG-13
SALGRADE 18-AUG-13
BONUS 18-AUG-13
EMP 18-AUG-13
DEPT 23-AUG-13
6 rows selected.
8) Revert the statistics of whole schema from the backup
SQL> EXECUTE DBMS_STATS.IMPORT_SCHEMA_STATS ('SCOTT','stats_bkp_scott');
PL/SQL procedure successfully completed.
9) Check the last analyzed date of the tables
SQL> select table_name, last_analyzed from DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME LAST_ANAL
------------------------------ ---------
STATS_BKP_SCOTT
DEPT 18-AUG-13
EMP 18-AUG-13
BONUS 18-AUG-13
SALGRADE 18-AUG-13
TEMP 18-AUG-13
6 rows selected.
