Hi, We are using version 11.2.0.4 of oracle. We have table(TAB1) in two different databases(PROD1,PROD2) which are always in sync and is as per business requirement. Due to certain application issue we found we have one of the database(DB1) table(TAB1) have some of the data not loaded properly i.e those data got missed. And its happening since last one month without our notice. Now that we found it , we want to fix this issue assuming we have all the correct data available in DB2 for TAB1. Missing rows is around ~200million and the table TAB1 is a daily range partitioned table. Each daily partition holds ~50million rows and having size ~20gb. We are tried using the minus operator and try inserting data into TAB1 of DB1 for one partition at a time using bulk collect same as below logic, but its very slow and all the time being spent over the DB link only for reading the rows from the remote database. It took around ~40hrs to find and insert missing data for one day partition to target table database using below logic.
1)Want to understand, what should be the fastest way in this type of scenario to fix the data issue in quick possible time?
2)Is it a good idea to export and import individual partitions from remote DB and truncate/load the partitions of local database, or it will cause any resource crunch?
Logic is as below:-
Cursor cur1 is
select * from tab1@dblink1 where part_date=input_date
minus select * from tab1 where part_date=input_date;
Inserting above data into TAB1 using bulk collect with arraysize 10k.