How to backup and restore statistics using dbms_stats

1)Check last analyzed date of scott schema tables
ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
SALGRADE                       09-FEB-2009 10:00:04
BONUS                          09-FEB-2009 10:00:04
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04


2)Create stat table in users tablespace
ORCL>>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
ORCL>>exec dbms_stats.export_schema_stats(ownname => 'SCOTT', stattab => 'stats_bkp_scott');
PL/SQL procedure successfully completed.


4)Take the export backup of scott schema or the owner of stats table
$ exp scott/scott1 file=scott_stat_bkp_09122010.dmp tables=scott.STATS_BKP_SCOTT
Export: Release 10.2.0.4.0 - Production on Thu Dec 9 14:37:42 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                STATS_BKP_SCOTT         24 rows exported
Export terminated successfully without warnings.


5)Gather the statistics
14:41:39 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'DEPT', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14

14:41:51 ORCL>>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.

14:42:58 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'BONUS', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.

14:43:19 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'SALGRADE', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.


6)Check the last analyzed date of tables
14:43:41 ORCL>> select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-DEC-2010 02:43:41
BONUS                          09-DEC-2010 02:42:59
EMP                            09-DEC-2010 02:42:27
DEPT                           09-DEC-2010 02:41:50


7)Import/Revert the statistics of one/two table from the backup
15:07:22 ORCL>>exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'emp', statown=>'scott', stattab=>'stats_bkp_scott', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.30

15:10:28 ORCL>>exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'dept', statown=>'scott', stattab=>'stats_bkp_scott', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26


8)Check the last analyzed date of the tables
15:120:31 ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-DEC-2010 02:43:41
BONUS                          09-DEC-2010 02:42:59
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04
Elapsed: 00:00:00.54


9)Revert the statistics of whole schema from the backup
15:40:38 ORCL>>EXECUTE DBMS_STATS.IMPORT_SCHEMA_STATS ('SCOTT','stats_bkp_scott');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.34


10)Check the last analyzed date of the tables
15:45:53 ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-FEB-2009 10:00:04
BONUS                          09-FEB-2009 10:00:04
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04
Elapsed: 00:00:00.27