1 创建原始表及数据
SQL> create table t1(id number, name varchar2(20));
Table created
SQL> insert into t1 values(1,'A');
1 row inserted
SQL> insert into t1 values(1,'B');
1 row inserted
SQL> insert into t1 values(2,'C');
1 row inserted
SQL> insert into t1 values(3,'D');
1 row inserted
SQL> insert into t1 values(3,'E');
1 row inserted
SQL> insert into t1 values(4,'F');
1 row inserted
SQL> commit;
Commit complete
2 在表上创建enable novalidate主键
SQL> alter table t1 add constraint pk_t1 primary key(id) enable novalidate;
Table altered
3 尝试将约束状态更改为 enable validate，却发现原有数据有问题
SQL> alter table t1 enable validate constraint pk_t1;
alter table t1 enable validate constraint pk_t1
ORA-02437: 无法验证 (MYHR.PK_T1) - 违反主键
4 找出违反约束的数据，这里采用Exceptions表保存异常数据
Table created
SQL> alter table t1 enable validate constraint pk_t1 exceptions into exceptions;
alter table t1 enable validate constraint pk_t1 exceptions into exceptions
ORA-02437: 无法验证 (MYHR.PK_T1) - 违反主键
5 根据异常表，找出对应源表中的异常数据
SQL> select id, name from t1 where rowid in(select row_id from exceptions);
ID NAME
---------- --------------------
1 A
1 B
3 D
3 E
6 修正异常数据后，将索引生效