Oracle deletes a table with a large volume of duplicate data and the table has a primary key being referenced.

When oracle deletes a table with a large amount of duplicate data and the table has a primary key referenced, clear the duplicate data. 1. Save the data to be retained and put it into the temporary table USERCOMPANYINFO_tmp.

Java code create table USERCOMPANYINFO_tmp as (select * from USERCOMPANYINFO where rowid not in (select. rowid from usercompanyinfo a, (select max (. ROWID) RROWID, company from usercompanyinfo a group by. company having count (*)> 3) B WHERE. COMPANY = B. company and. ROWID <> B. RROWID ))

 

2 when the primary key in the table is referenced by other tables, you need to execute the following statement otherwise the error will be reported: Java code ORA-02266: unique/primary keys in table referenced by enabled foreign keys ORA-02266: unique/primary key-enabled external keyword reference in the table

Java code alter table USERCOMPANYINFO disable primary key cascade; // clear external references

 

3

Truncate table USERCOMPANYINFO; // clear data

 

4

Insert into USERCOMPANYINFO select * from USERCOMPANYINFO_tmp; // import data from the temporary Jiang table to the target data table

 

5

Alter table USERCOMPANYINFO enable primary key; // Add a primary key constraint to the target data table