How to export and import statistics in oracle

 

 1. Create Statistics table to store stats.

exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_TABLE',tblspace=>'USERS');

2. Export the Stats of Schema or table.

For Schema: 

exec dbms_stats.export_schema_stats(ownname => 'SCOTT',stattab => 'STAT_TABLE');

For Table : 

exec dbms_stats.export_table_stats(ownname => 'SCOTT',tabname => 'EMPLOYEES',stattab => 'STAT_TABLE');

3. Take expdp of this stats table.

expdp dumpfile=stats.dmp logfile=stats.log tables=SCOTT.STAT_TABLE directory=DATA_PUMP_DIR

4. Move the dump file from source to target server and import the stats table.

impdp dumpfile=stats.dmp logfile=stats_import.log tables=SCOTT.STAT.TABLE directory=DATA_PUMP_DIR table_exists_action=REPLACE

5. Import the Stats of Schema or Table in databases

For Schema Stats:

exec dbms_stats.import_schema_stats(ownname => 'SCOTT',stattab => 'STAT_TABLE');

For Table Stats:

exec dbms_stats.import_table_stats(ownname => 'SCOTT',tabname => 'EMPLOYEES',stattab => 'STAT_TABLE');

6. Drop Stats table if not required.

exec dbms_stats.drop_stat_table(ownname => 'SCOTT', stattab => 'STAT_TABLE');