How to Copy Table stats from one database to another database

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';

TNAME TABTYPE CLUSTERID

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

STAT_TAB TABLE

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.

RESTORE_DIR /oracle/stage/RESTORE_DIR

 cat > exp_stat.ctl

userid=APPS/apps

 directory=DATA_PUMP_DIR

 tables=STAT_TAB

 dumpfile=exp_stat.dmp

 LOGFILE=exp_stat.log

..

 . . 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

 userid=APPS/apps

 directory=RESTORE_DIR

 tables=STAT_TAB

 dumpfile=exp_stat.dmp

 LOGFILE=imp_stat.log

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