使用exception来查找重复的数据

今天建一个unique索引失败，找到了重复的值。

1.首先用下面的sql语句创建

create unique index IND_AB08_AAE140_AAE002_AAE003 on AB08 (AAE140, AAE002, AAE003, AAB001, AAC001, AAE143, AAA060, AAA070) tablespace ts_hmsimis_ind_a ;

重复ORA-02299: 无法验证 (HMSIMIS.IND_AB08_AAE140_AAE002_AAE003) - 找到重复关键字

2.换一种方法创建这个唯一索引

ALTER TABLE ab08 ADD CONSTRAINT IND_AB08_AAE140_AAE002_AAE003 UNIQUE (AAE140, AAE002, AAE003, AAB001, AAC001, AAE143, AAA060, AAA070) using index tablespace ts_hmsimis_ind_a;

一样出现无法验证找到重复关键字的错误。

3.通过exceptions来查找重复的值

首先创建一个异常表  ：

create table except_ab08(row_id urowid,
                        owner varchar2(30),
                        table_name varchar2(30),
                        constraint varchar2(30));

可以通过rdbms/utlexp1.sql创建这个表，与上面的结构是一样的。

当在schema中有exceptions这个表时，则不用 exceptions into xxx语句，否则需要。

再执行语句：

ALTER TABLE ab08 ADD CONSTRAINT IND_AB08_AAE140_AAE002_AAE003 UNIQUE (AAE140, AAE002, AAE003, AAB001, AAC001, AAE143, AAA060, AAA070) using index tablespace ts_hmsimis_ind_a exceptions into except_ab08;

查询表可以看到

SQL> select * from except_ab08;

ROW_ID                                                                           OWNER                          TABLE_NAME                     CONSTRAINT
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
AAAMysAAJAAAXSFAAZ                                                               HMSIMIS                        AB08                           IND_AB08_AAE140_AAE002_AAE003
AAAMysAAJAAABL3AAa                                                               HMSIMIS                        AB08                           IND_AB08_AAE140_AAE002_AAE003
AAAMysAAJAAAXSFAAa                                                               HMSIMIS                        AB08                           IND_AB08_AAE140_AAE002_AAE003
AAAMysAAJAAABL3AAZ                                                               HMSIMIS                        AB08                           IND_AB08_AAE140_AAE002_AAE003
AAAMysAAJAAAXSFAAo                                                               HMSIMIS                        AB08                           IND_AB08_AAE140_AAE002_AAE003
AAAMysAAJAAABL3AAm                                                               HMSIMIS                        AB08                           IND_AB08_AAE140_AAE002_AAE003

然后在ab08中可以找到这个行，再根据索引的重复值去查找就可以找到重复的记录。

SQL> select * from ab08 where rowid='AAAMysAAJAAAXSFAAZ';

最后再修改数据或索引