Transferring table statistics from Instance 1 to Instance 2

Following steps need to be done to transfer statistics of a table from Instance 1 to Instance 2.

• create stats table
• export stats to this stats table
• export newly created stats table
• transfer export dump file to other server
• import this dump file
• import stats from newly imported stats table 

On Instance 1

Step 1) Create stats table
exec dbms_stats.create_stat_table('STSC','STAT_PROCESSSKU_MAR16');
PL/SQL procedure successfully completed.

Step 2) Export stats for a particular table to this stats table
SQL> exec dbms_stats.export_table_stats(ownname =>'STSC',tabname =>'PROCESSSKU',stattab =>'STAT_PROCESSSKU_MAR16',cascade =>true,statown =>'STSC');
PL/SQL procedure successfully completed.

Step 3) Export stats_table using Oracle export
$ exp / file=exp_stsc_processsku_stats_from_pjda1.dmp tables=STSC.STAT_PROCESSSKU_MAR16 log= exp_stsc_processsku_stats_from_pjda1.log

On Instance 2
Step 1) Create stats table
SQL> exec dbms_stats.create_stat_table('STSC','STAT_PROCESSSKU_MAR15');
PL/SQL procedure successfully completed

Step 2) Export stats for a particular table to this stats table
SQL> exec dbms_stats.export_table_stats(ownname =>'STSC',tabname =>'PROCESSSKU',stattab =>'STAT_PROCESSSKU_MAR15',cascade =>true,statown =>'STSC');
PL/SQL procedure successfully completed.

Step 3) Export stats_table using Oracle export (Optional , just for additional backup)
$ exp / file=exp_stsc_processsku_stats_from_qjda1.dmp tables=STSC.STAT_PROCESSSKU_MAR15 log= exp_stsc_processsku_stats_from_qjda1.log
Export: Release 10.2.0.4.0 - Production on Wed Mar 16 10:24:29 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to STSC
. . exporting table STAT_PROCESSSKU_MAR15 19 rows exported
Export terminated successfully without warnings.

Step 4) Transfer exp_stsc_processsku_stats_from_pjda1.dmp file from Instance 1 to Instance 2 for import in Instance 2

Step 5) Import stats table from pjda1 using imp command in Instance 2
$ imp / file=exp_stsc_processsku_stats_from_pjda1.dmp full=y log=imp_stsc_processsku_stats_from_pjda1.log

Step 6) Unlock schema stats if it is already locked (Optional. In my case needed)
SQL> exec DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'STSC');
PL/SQL procedure successfully completed.

Step 7) Import stats for STSC.PROCESSSKU table and its index from STSC.STAT_PROCESSSKU_MAR16 stats table
SQL> exec dbms_stats.import_table_stats(ownname =>'STSC',tabname =>'PROCESSSKU',stattab =>'STAT_PROCESSSKU_MAR16',cascade =>true,statown =>'STSC');
PL/SQL procedure successfully completed.

Step 8) Lock schema stats again
SQL> exec DBMS_STATS.LOCK_SCHEMA_STATS(ownname => 'STSC'); (Again optional in your case)
PL/SQL procedure successfully completed.