Home > Uncategorized > Export Import Table Stats from UAT system into Production

Export Import Table Stats from UAT system into Production

steps to export table stats from one system to another

UAT SYSTEM

Unlock the stats on the customer  table


exec dbms_stats.unlock_table_stats(ownname => scott, tabname => 'CUSTOMER');

Oracle will gather stats via the overnight or weekend job window

create table for Exports of customer table stats


execute dbms_stats.create_stat_table(ownname= 'scott', stattab= 'backup_stats_uat');

Import the stats from the customer table and indexes


exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'customer', statown=>'scott', stattab=>'backup_stats_uat', cascade=>true);

Use datapump to export the table


expdp system tables=scott.backup_stats_uat directory=oraexp dumpfile=backup_stats_uat.dmp logfile=backup_stats_uat.log

Transfer the file to the production system

PRODUCTION SYSTEM

unlock stats on table scott.customer


exec dbms_stats.unlock_table_stats(ownname => scott, tabname => 'customer')

Import the table backup_stats_uat using the backup_stats_uat.dmp


impdp system tables=scott.backup_stats_uat directory=data_pump_dir dumpfile=backup_stats_uat.dmp logfile=imp_backup_stats_uat.log

Once table is imported /import the table and index stats


exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'customer', statown=>'scott', stattab=>'backup_stats_uat', cascade=>true);

drop the backup_stats_uat table


execute dbms_stats.drop_stat_table(ownname= 'npp', stattab= 'backup_stats_uat');

If you need to regress the stats


exec dbms_stats.restore_table_stats ('scott','customer',sysdate-1);

 

 

 

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: