How to Export and Import Database Statistics
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 you 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('MYUSER','MYSTATSTABLE','MYTABLESPACE');
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('MYSTATSTABLE','MYRELEASE2','MYUSER');
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('MYSTATSTABLE','MYRELEASE2','MYUSER');