Database Statistics Export/Import

Introduction

There may be times that you wish to save you database statistics. Perhaps to use in a test system or prior to a code release that may ultimately change your statistics and change your query plans for the worse. By exporting the statistics, they can be saved for future importing.

Step by Step Export of Database Statistics

1. Log onto the database 
      sqlplus '/ as sysdba'

2. Create a table to hold the exported statistics. 
  SQL> exec DBMS_STATS.CREATE_STAT_TABLE('<table owner>','<enter a name for the stats      table>','<tablespace to store the stats table');

For example; exec dbms_stats.create_stat_table('SCOTT','MYSTATTABLE','USERS');

3. Export the database statistics 
SQL> exec dbms_stats.EXPORT_DATABASE_STATS('<enter the name of the stats table>','<enter an identifier>','<enter the owner of the stats table>');

For example;

exec dbms_stats.export_database_stats('MYSTATTABLE','R1','SCOTT');

Now Export this Table with Exp utility.

exp scott/password file=mystattable.dmp tables=mystattable

And Import in Target DB.

imp scott/password file=mystattable.dmp full=y

Step by Step Import of Database Statistics

1. Log onto the database 
  sqlplus '/ as sysdba'

2. Import the database statistics

SQL> exec dbms_stats.IMPORT_DATABASE_STATS('<enter the name of the stats table>','<enter an identifier>','<enter the owner of the stats table>');

For example; 
exec dbms_stats.IMPORT_DATABASE_STATS('STATTABLE','R2','SCOTT');