Capture and Transfer Oracle Statistics

The dbms_stats Oracle-supplied package makes it easy to back up and restore statistics.

Create the table where statistics will be captured. dbms_stats.create_stat_table

SQL> exec dbms_stats.create_stat_table('SCHEMAOWNER', 'STATTAB')
PL/SQL procedure successfully completed.

dbms_stats.export_schema_stats
This requires a table name where the statistics will be stored. This table can be exported (like any other table) and imported into a development database.

dbms_stats.import_schema_stats


Example of capturing and transferring statistics:

SQL> exec dbms_stats.export_schema_stats('SCHEMAOWNER','STATTAB')
PL/SQL procedure successfully completed.


$ exp file=stattab.dmp tables=SCHEMAOWNER.stattab compress=n

About to export specified tables via Conventional Path...
Current user changed to SCHEMAOWNER
. . exporting table STATTAB
8701 rows exported
Export terminated successfully without warnings.


>>> Copy export file and change ORACLE_SID as appropriate.


$ sqlplus SCHEMAOWNER@qa
SQL> delete from stattab;


$ imp file=stattab.dmp fromuser=SCHEMAOWNER touser=SCHEMAOWNER ignore=y

Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SCHEMAOWNER's objects into SCHEMAOWNER
. . importing table "STATTAB"
8701 rows imported
Import terminated successfully without warnings.


$ sqlplus SCHEMAOWNER@qa
SQL> exec dbms_stats.import_schema_stats('SCHEMAOWNER','STATTAB')
PL/SQL procedure successfully completed.