How to backup and restore statistics using dbms_stats

1) Check last analyzed date of scott schema tables

PREPROD>>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

PREPROD>>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

PREPROD>>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 PREPROD>>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 PREPROD>>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 PREPROD>>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 PREPROD>>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 PREPROD>> 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 PREPROD>>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 PREPROD>>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 PREPROD>>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 PREPROD>>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 PREPROD>>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