如何在oracle数据库中删除重复的记录(转)

平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时，系统提示 ORA-01452 ：不能创建唯一索引，发现重复记录。

下面总结一下几种查找和删除重复记录的方法（以表CZ为例）：

表CZ的结构如下：

?

SQL>@?/rdbms/admin/utlexcpt.sqlTable created.SQL>desc exceptionsName Null? Type----------------------------------------- ROW_ID ROWIDOWNER VARCHAR2(30)TABLE_NAME VARCHAR2(30)CONSTRAINT VARCHAR2(30)SQL>alter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into exceptions; *ERROR at line 1:ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys foundSQL>create table dups as select * from cz where rowid in (select row_id from exceptions); Table created.SQL>select * from dups; C1 C10 C20---------- ---------- ---1 2 dsf1 2 dsf1 2 dsf1 2 dsf2 3 che1 2 dsf1 2 dsf1 2 dsf1 2 dsf2 3 che2 3 che2 3 che2 3 che3 4 dff3 4 dff3 4 dff16 rows selected.SQL>select row_id from exceptions;ROW_ID------------------AAAHD/AAIAAAADSAAAAAAHD/AAIAAAADSAABAAAHD/AAIAAAADSAACAAAHD/AAIAAAADSAAFAAAHD/AAIAAAADSAAHAAAHD/AAIAAAADSAAIAAAHD/AAIAAAADSAAGAAAHD/AAIAAAADSAADAAAHD/AAIAAAADSAAEAAAHD/AAIAAAADSAAJAAAHD/AAIAAAADSAAKAAAHD/AAIAAAADSAALAAAHD/AAIAAAADSAAMAAAHD/AAIAAAADSAANAAAHD/AAIAAAADSAAOAAAHD/AAIAAAADSAAP16 rows selected.SQL>delete from cz where rowid in ( select row_id from exceptions);16 rows deleted.SQL>insert into cz select distinct * from dups;3 rows created.SQL>select *from cz;C1 C10 C20---------- ---------- ---1 2 dsf2 3 che3 4 dff4 5 err5 3 dar6 1 wee7 2 zxc7 rows selected.

从结果里可以看到重复记录已经删除。

?