How to Backup and Restore Oracle Statistics

Following are the few tips:

To Backup the STATS

• Create a table that will hold the STATS backup

           exec dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'STATS_BACKUP', tblspace => 'TOOLS');

This example, creates a table STATS_BACKUP under SYSTEM user and uses TOOLS tablespace for data storage.

• Run the export command to capture the current stats into the backup table. (I suggest to export the entire SCHEMA as a safety net).
  exec dbms_stats.export_schema_stats(ownname => 'SCOTT', stattab => 'STATS_BACKUP', statid => 'JAN192011' , statown => 'SYSTEM');

          In this example, the SCOTT schema STAT is backed up to STATS_BACKUP table.

• Verify if the export of the STATS by issuing this query
  select * from system.stats_Backup where statid = 'JAN192011';

To Restore STATS

There are couple of ways you can adopt to restore the STATS. Before restoring the STATS, always backup the existing STATS using the method described above.

Method 1: Restoring the STATS through the Oracle Base table for a particular TABLE.

a. Find out if that particular table has STATS history.

select to_char(t.stats_update_time, 'mm/dd/yyyy hh24:mi:ss'), t.*
from   DBA_TAB_STATS_HISTORY t
where    owner = 'SCOTT'
and      table_name = 'DEPT';

b. If there is a record returned by the above query, narrow it down to the right date and time you want to restore back to.

c. Issue this command to restore the STATS

   EXEC DBMS_STATS.RESTORE_TABLE_STATS('SCOTT' ,'DEPT', to_Date('01/11/2011 19:42:24', 'MM/DD/YYYY HH24:MI:SS'));

Method 2: Restoring the STATS from backup for a particular TABLE.

Sometimes when you want to go beyond 30 days (AWR retention is normally 30 days and the above step may not work since the STAT history gets purged), you will have to restore the STATS from the backup. The steps include the following.

a. Query the STATS Backup TABLE (SYSTEM.STATS_BACKUP) and find out the details related to that TABLE.

   select * from system.stats_Backup where c1 = '<tablename>';


b. From the above query, get the STATID you want to restore back to. Pay careful attention to the data/time for that STATID

c. Import the STATS back for that particular table.

   exec dbms_stats.import_Table_stats(ownname => 'SCOTT', stattab => 'STATS_BACKUP', statid => 'JAN192011' , 
                                            statown => 'SYSTEM', TABNAME => 'tablename');

Method 3: Restoring the STATS from backup from QAS to PROD.

a. Export the STATS table (SYSTEM.STATS_BACKUP) from the source database.

         Parfile for typical expdp

         dumpfile=expdp_stats_backup.dmp
         logfile=expdp_stats_backup.log
         directory=DATA_PUMP_DIR
         EXCLUDE=STATISTICS
         CONTENT=DATA_ONLY
         tables=STATS_BACKUP


b. Create the stats table in the destination database if it does not exists.

          exec dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'STATS_BACKUP', tblspace => 'TOOLS'); 
c. Import the stats into the destination database.

          dumpfile=expdp_stats_backup.dmp
          logfile=impdp_stats_backup.log
          directory=DATA_PUMP_DIR
          CONTENT=DATA_ONLY
          tables=STATS_BACKUP

 d.  Use the dbms_stats package to import the SCHEMA stats or STATS for a table.

          Eg. exec dbms_stats.import_schema_stats(ownname => 'SCOTT', stattab => 'STATS_BACKUP', statid => 'JAN192011' , 
                                            statown => 'SYSTEM');     -- To import the stats for the schema


          Eg. exec dbms_stats.import_Table_stats(ownname => 'SCOTT', stattab => 'STATS_BACKUP', statid => 'JAN192011' , 
                                            statown => 'SYSTEM', TABNAME => 'tablename');  -- To import the stats for a Table.


If you are importing stats from PROD to DEV/QA between two different schemas, make sure the SCHEMA owners are same before trying the import_schema_stats or import_table_stats. If the schema owners are not the same, you will have to 
update the stats backup table.

The following example shows the update statement if the SCHEMA OWNERS are different. 

         update system.stats_backup
         set   c5 = 'SCOTTPROD'
         where c5 = 'SCOTT';