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表保存异常数据
SQL> @%ORACLE_HOME%RDBMS/ADMIN/utlexpt1.sql;
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修正异常数据后,将索引生效