Oracle removes duplicate data

Background: There are two databases (source database, and target database), each day to the source database data synchronization to the target database, for various reasons, afraid of data loss, all synchronization 8 days before and after the data (there is a primary key, do not worry about repetition, a hundred thousand of per day, the table has 60 million), But I do not know the day when a colleague mistakenly drop the primary key.

Statistics of BI report data is much more outrageous. After a toss-up, the problem solved. Here are a few ways to summarize:

1) Flashback: Oracle has flashback technology that can use the RecycleBin (Recycle Bin) to query the deleted primary key, but this will remove the duplicate data.

2) Use rowID to query for duplicate data and kill the same data except ROWID minimum, statement:

                          delete fro M table A where (A.ID,A.SEQ) in (Select Id,seq from table group by ID,SEQ have count (*) > 1) and rowID not in (select min (rowid ) from table group by ID,SEQ have Count (*) >1)

                  This DML statement is a nightmare because there is "not in" if your data volume is large, please use caution.                3) This is a practical approach, and efficiency can be removed in about 5 minutes. The steps are as follows:                       1. Querying the table for duplicate data       &N Bsp                         SELECT * FROM table 1 a where  (a.id,a. SEQ) in (select Id,seq from table 1 GROUP by ID,SEQ have count (*) > 1)   (A. Id,a.seq is a duplicate primary key)                         2. Build a table   &NB Sp                              create table LSB A S select * FROM table 1 a where  (A.ID,A.SEQ) in (select Id,seq from table 1 GROUP by ID,SEQ have count (*) > 1); Commit;(so the LSB table structure is the same as Table 1 table structure)                         3. DeleteDuplicate data in table 1                          delete from table 1 a where (A.ID,A.SEQ) in (select Id,seq from table 1 GROUP by ID,SEQ have count (*) > 1);            &N Bsp            commit;                   &NBSP ;    4. Querying the LSB table for ROWID minimum data                         &NBSP ; SELECT * from LSB a where  a.rowid in (select min (rowid) from LSB GROUP by ID,SEQ have count (*) > 1)  &nb Sp                                  &NBS P          5. Insert query rowID into table 1                           INSERT INTO table 1 select * from LSB a where  a.rowid in (select min (rowid) from LSB Group by Id,sEQ having count (*) > 1;                          COMM it;                                 () nbsp            6.drop table lsb;           4) Overall step-up   &nbsp ;                      CREATE TABLE LSB as select * FROM table 1 a WH ere  (A.ID,A.SEQ) in (select Id,seq from table 1 GROUP by ID,SEQ have count (*) > 1); --can also be a temporary table more efficient (no write disk required)  commit;                        &NB Sp                           delete from table 1 a where (a. ID,A.SEQ) in (select Id,seq from table 1 GROUP by ID,SEQ have count (*) > 1);            &NBSP ;            commIt;  insert into Table 1 select * from LSB a where  a.rowid in (select min (rowid) from LSB Group by Id, SEQ have count (*) > 1);                         comm it;                          drop table LSB;

Oracle Delete duplicate data