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