Table Refresh step by step

Below are details step by step for table refresh . Points to be remember
a. This steps is very useful when we are refreshing tables that has referential constraints.
b. If there is no referential constraints we can use table_exists_action=replace parameter during import.


1. Check the size of the table in the source and target.
And that much space is available in the mount point or not.

sql> select segment_name,segment_type,bytes/1024/1024
 from dba_segments where segment_type='TABLE' and segment_name='<table_name>';

2.Take the backup of the table in the target database.
 $expdp dumpfile=backup_Table_%U.dmp logfile=backup_Table.log directory=<directory name> tables=schema.tablename parallel=<n> compression=all

3.create spool file for Disable the contraints for the tables.
sql>spool disable_contraints.sql

sql> set lines 200 pages 300
   select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name in('tables_name');
sql> spool off

4.create spool for enable the constraints.

sql>spool enable_contraints.sql

sql> set lines 200 pages 300
   select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where table_name in('tables_name');

 sql> spool off;

5.alter session set current_schema=schema_name

6. run @disable_contraints.sql

7.Either Drop the table in the target database or use TABLE_EXISTS_ACTION=REPLACE  parameter during import 

sql> Drop table schema.table_name;

8.Import the dump taken from source.

$impdp dumfile=export_%U.dmp logfile=import.log directory=<directory_name> tables=schema.tablename parallel=<n>

 --monitor the  log--

9.check the row count in the target after refresh.

sql> Select count(*) from schema.tablename;

10.check the row count in the source database . 

sql> Select count(*) from schema.tablename;

-- count should be same.---

11.alter session set current_schema=schema
run @enable_contraints.sql


------------------------------ table refresh is complete------------------------