Backout Plan using Data Pump for deleting records on table and then re-inserting
Posted: Sat Dec 08, 2012 6:17 pm
1. Export:
nohup expdp "" '/ as sysdba' "" directory=DBA_EXP dumpfile=emjo.table1_table2_%u.dmp tables=user.table1,user.table2 logfile=expdp_table1_table2.log &"
2. Delete records.
3. Backout
A) Import the 2 tables into a new table name.
A-1
nohup impdp " '/ as sysdba' " directory=DBA_EXP dumpfile=user.table2_table1_20121207_01.dmp tables=user.table1 REMAP_TABLE=user.table1:table1_new exclude=INDEX logfile=impdp_table1.log &
A-2
nohup impdp " '/ as sysdba' " directory=DBA_EXP dumpfile=user.table2_table1_20121207_01.dmp tables=user.table2 REMAP_TABLE=user.table2:table2_new exclude=INDEX logfile=impdp_table2.log &
B) Re-insert the previously deleted records to the original tables.
insert into "user"."table1" select * from "user"."table1_new"
where [enter where clause here from your previous delete statement];
insert into "user"."table2" select * from "user"."table2_new"
where [enter where clause here from your previous delete statement];
nohup expdp "" '/ as sysdba' "" directory=DBA_EXP dumpfile=emjo.table1_table2_%u.dmp tables=user.table1,user.table2 logfile=expdp_table1_table2.log &"
2. Delete records.
3. Backout
A) Import the 2 tables into a new table name.
A-1
nohup impdp " '/ as sysdba' " directory=DBA_EXP dumpfile=user.table2_table1_20121207_01.dmp tables=user.table1 REMAP_TABLE=user.table1:table1_new exclude=INDEX logfile=impdp_table1.log &
A-2
nohup impdp " '/ as sysdba' " directory=DBA_EXP dumpfile=user.table2_table1_20121207_01.dmp tables=user.table2 REMAP_TABLE=user.table2:table2_new exclude=INDEX logfile=impdp_table2.log &
B) Re-insert the previously deleted records to the original tables.
insert into "user"."table1" select * from "user"."table1_new"
where [enter where clause here from your previous delete statement];
insert into "user"."table2" select * from "user"."table2_new"
where [enter where clause here from your previous delete statement];