How to Copy Table stats from one database to another database

If we want to migrate the stats from One database to other database, we can follow below steps.

1) Create a stat table in the source database. The statistics table is created in APPS schema.

connect as APPS

exec dbms_stats.create_stat_table(ownname => 'APPS', stattab => 'STAT_TAB');

select * from tab where TNAME='STAT_TAB';

------------------------------ ------- ----------

2) Export the table statistics.

exec dbms_stats.export_table_stats(ownname=>'INV',tabname=>'MTL_SYSTEM_ITEMS_TL',statown=>'APPS',stattab=>'STAT_TAB',cascade=>TRUE);

3) Then take export backup of table STAT_TAB from schema APPS. COPY the dump file to target server. I did on shared path.


 cat > exp_stat.ctl

 . . exported "APPS"."STAT_TAB" 17.5 KB 28 rows

4) Import the dump file into target database by using impdp or imp utility. I imported the dump file in APPS Schema at target server.

 cat > imp_stat.ctl

Run Import
impdp parfile=imp_stat.ctl

6) Import the statistics into application schemas

Deleting existing stats on tables

exec dbms_stats.delete_table_stats(ownname=>'INV',tabname=>'MTL_SYSTEM_ITEMS_TL',cascade_indexes => true,cascade_columns => true);

Import New stats for tables

exec dbms_stats.import_table_stats(ownname=>'INV',tabname=>'MTL_SYSTEM_ITEMS_TL',statown=>'APPS',stattab=>'STAT_TAB',cascade=>TRUE);

select owner,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where table_name in('MTL_SYSTEM_ITEMS_TL') and owner='INV';

7) Drop the stats_table in target server.

 EXEC DBMS_STATS.drop_stat_table('APPS','STAT_TAB');

Just for Information:

Delete stats 
 For Table
 Exec dbms_stats.delete_table_stats (
 ownname => 'INV',
 tabname => 'MTL_SYSTEM_ITEMS_TL',
 cascade_indexes => true,
 cascade_columns => true

For Schema
 exec dbms_stats.export_schema_stats('INV','dictstattab',statown => 'APPS');

If you like please follow and comment