2007年3月 Oracle大版内专家分月排行榜第二
2006年10月 Oracle大版内专家分月排行榜第三2006年9月 Oracle大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。Oracle几种查找和删除重复记录的方法总结
发表于 17:08|
来源IT专家网|
作者chenqiuge1984
摘要：平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时，系统提示 ORA-01452 ：不能创建唯一索引，发现重复记录。 下面总结一下几种查找和删除重复记录的方法（以表
平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时，系统提示 ORA-01452 ：不能创建唯一索引，发现重复记录。
下面总结一下几种查找和删除重复记录的方法（以表CZ为例）：
表CZ的结构如下：
SQL& desc cz
Name Null? Type
C1 NUMBER(10)
C10 NUMBER(5)
C20 VARCHAR2(3)
删除重复记录的方法原理：
(1).在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
(2).在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。
重复记录判断的标准是：
C1，C10和C20这三列的值都相同才算是重复记录。
经查看表CZ总共有16条记录：
SQL&set pagesize 100
SQL&select * from
C1 C10 C20
20 rows selected.
1.查找重复记录的几种方法：
(1).SQL&select * from cz group by c1,c10,c20 having count(*) &1;
C1 C10 C20
(2).SQL&select distinct *
C1 C10 C20
(3).SQL&select * from cz a where rowid=(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
C1 C10 C20
2.删除重复记录的几种方法：
(1).适用于有大量重复记录的情况（在C1，C10和C20列上建有索引的时候，用以下语句效率会很高）：
SQL&delete from cz where (c1,c10,c20) in (select c1,c10,c20 from cz group by c1,c10,c20 having count(*)&1) and rowid not in
(select min(rowid) from cz group by c1,c10,c20 having count(*)&1);
SQL&delete fron cz where rowid not in(select min(rowid) from cz group by c1,c10,c20);
(2).适用于有少量重复记录的情况（注意，对于有大量重复记录的情况，用以下语句效率会很低）：
SQL&delete from cz a where a.rowid!=(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL&delete from cz a where a.rowid&(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL&delete from cz a where rowid &(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
(3).适用于有少量重复记录的情况（临时表法）：
SQL&create table test as select distinct * (建一个临时表test用来存放重复的记录)
SQL& (清空cz表的数据，但保留cz表的结构)
SQL&insert into cz select * (再将临时表test里的内容反插回来)
(4).适用于有大量重复记录的情况(Exception into 子句法)：
采用alter table 命令中的 Exception into 子句也可以确定出库表中重复的记录。这种方法稍微麻烦一些，为了使用&excepeion into &子句，必须首先创建 EXCEPTIONS 表。创建该表的 SQL 脚本文件为 utlexcpt.sql 。对于win2000系统和 UNIX 系统， Oracle 存放该文件的位置稍有不同，在win2000系统下，该脚本文件存放在$ORACLE_HOME\Ora90\rdbms\admin 目录下;而对于 UNIX 系统，该脚本文件存放在$ORACLE_HOME/rdbms/admin 目录下。
具体步骤如下：
SQL&@?/rdbms/admin/utlexcpt.sql
Table created.
SQL&desc exceptions
Name Null? Type
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
SQL&alter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into
ERROR at line 1:
ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found
SQL&create table dups as select * from cz where rowid in (select row_id from exceptions);
Table created.
SQL&select * from
C1 C10 C20
16 rows selected.
SQL&select row_id from
AAAHD/AAIAAAADSAAA
AAAHD/AAIAAAADSAAB
AAAHD/AAIAAAADSAAC
AAAHD/AAIAAAADSAAF
AAAHD/AAIAAAADSAAH
AAAHD/AAIAAAADSAAI
AAAHD/AAIAAAADSAAG
AAAHD/AAIAAAADSAAD
AAAHD/AAIAAAADSAAE
AAAHD/AAIAAAADSAAJ
AAAHD/AAIAAAADSAAK
AAAHD/AAIAAAADSAAL
AAAHD/AAIAAAADSAAM
AAAHD/AAIAAAADSAAN
AAAHD/AAIAAAADSAAO
AAAHD/AAIAAAADSAAP
16 rows selected.
SQL&delete from cz where rowid in ( select row_id from exceptions);
16 rows deleted.
SQL&insert into cz select distinct * from
3 rows created.
SQL&select *from
C1 C10 C20
7 rows selected.
从结果里可以看到重复记录已经删除。
原文链接：
推荐阅读相关主题：
网友评论有（0）
CSDN官方微信
扫描二维码,向CSDN吐槽
微信号：CSDNnews
相关热门文章Oracle中imp导入相同的两个用户的数据_Oracle教程_
Oracle中imp导入相同的两个用户的数据
来源：人气：4654
　　为WIN2Kserver,为817　　用户user022004对应表空间user022004用户user072004对应表空间user072004，现想将user072003用户的数据导入到user072004中。　　经过查找论坛中的资料总结如下:
　　eXP user022004/user022004@jhzy file=f:ackupuser022004.dmp 　　OWNER=user022004 log=f:ackupuser022004.log　　create user user072004 identified by user072004 default 　　tablespace user072004;　　GRANT CONNECT,RESOURCE,IMP_FULL_DATABASE TO user072004;　　revoke unlimited tablespace from user072004;　　alter user user072004 default tablespace user072004 quota 　　unlimited on user072004;　　imp user072004/user072004@jhzy file=f:ackupuser022004.dmp 　　ignore = y fromuser = user022004 touser = user072004 log=f:ackupuser072004.log　　关健是revoke unlimited tablespace from user072004;　　和alter user user072004 default tablespace user072004 quota 　　unlimited on user072004;
优质网站模板9210人阅读
这两天要进行数据的导入导出，也学习了一下在linux下使用oracle的exp与imp命令。
一、先说exp：这是用于导出数据用的命令，本次主要用它来把几个表的数据导出来。命令如下：
exp oracleuser/passwd file=/mydata/dat.dmp tables=(table1,table2,table3)。其中/mydata是用于存放数据文件的目录。
如果数据很多，导出来的文件就可能很大，而操作系统对单个文件的大小又有限制时就要分割成几个文件来导出，如下：
exp oracleuser/passwd file=(/mydata/dat1.dmp,/mydata/dat2.dmp,mydata/dat3.dmp) tables=(table1,table2,table3) filesize=1.9G。这样，一个文件到了1.9G后写到第二个文件中。
二、说imp，导入：
这是用于导入数据的，命令如下：
imp oracleuser/passwd file=/myimpdat/dat.dmp ignore=y tables=(table1,table2,table3)
如果在导出的时候进行了文件分割就可以一条命令中导入几个文件：
imp oracleuser/passwd file=(/myimpdat/dat1.dmp,/myimpdat/dat2.dmp) ignore=y tables=table1,table2,table3)
如果导出时进行了文件分割，在导出时又没有把所有文件都写命令中，oracle会一个个地询问的文件路径，此时把文件路径输进去，也可以进行导入。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：664351次
积分：5407
积分：5407
排名：第3958名
原创：112篇
转载：75篇
评论：22条
(1)(1)(2)(4)(1)(1)(2)(3)(1)(1)(1)(1)(1)(2)(4)(2)(2)(2)(1)(1)(1)(3)(4)(2)(2)(1)(2)(4)(2)(5)(1)(2)(1)(3)(2)(1)(1)(3)(1)(2)(1)(1)(2)(3)(1)(4)(5)(1)(2)(2)(2)(18)(5)(63)