Export Database Statistics and Import in another


If you want to test a certain set of statistics in one and use it in another. Here is how to do this. You could even consider to collect statistics initially on a look a like database and use is to safe time in your production environment , might you want to re-gather all statistics there. Also you can ‘safe’ a set of statistics like this and import it back again when something is off.

Create Statistics staging Table and fill it with current stats


exec DBMS_STATS.CREATE_STAT_TABLE('SYS','DB_STATISTICS','STATSPACK');

exec DBMS_STATS.EXPORT_DATABASE_STATS('DB_STATISTICS','NOMP1_01062016','SYS');

PL/SQL procedure successfully completed.

Export Table for shipping


[oracle@nompdb1 NOMP1 ~]$ exp userid='"'/ as sysdba'"' file=NOMP1_01062016_DBStats.dmp log=NIMP1_01062016_exp_DBStats.log tables=DB_STATISTICS rows=yes
..
About to export specified tables via Conventional Path ...
. . exporting table DB_STATISTICS 292743 rows exported
Export terminated successfully without warnings.

Transfer the dump

Please find the correct way for your environment here.

Import it into another database staging table


[oracle@nomxdb1 NOMA1 ~]$ imp userid='"'/ as sysdba'"' file=NOMP1_01062016_DBStats.dmp log=NOMP1_01062016_imp_DBStats.log tables=DB_STATISTICS rows=yes
..
Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "DB_STATISTICS" 292743 rows imported
Import terminated successfully without warnings.

Import into Database from staging table


exec dbms_stats.IMPORT_DATABASE_STATS('DB_STATISTICS','NOMP1_01062016','SYS');

Dropping Statistics Staging Table again( NOMP1 )


SQL> exec DBMS_STATS.DROP_STAT_TABLE('SYS','DB_STATISTICS');

PL/SQL procedure successfully completed.

SQL>