Monday, November 06, 2006

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.

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.


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.

No comments:

Post a Comment