Data check when Oracle restricts enable validate

1. Create the original table and Data

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. Create an enable novalidate primary key in the table

SQL> ALTER TABLE T1 add constraint pk_t1 primary key (ID) Enable novalidate;

Table altered

3. Change the constraint status to enable validate, but the original data is faulty.

SQL> ALTER TABLE T1 enable validate constraint pk_t1;

Alter table T1 enable validate constraint pk_t1

ORA-02437: unable to verify (Myhr. pk_t1)-primary key Violation

4. Identify the data that violates the constraints. Here, the exceptions table is used to save the exception data.

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: unable to verify (Myhr. pk_t1)-primary key Violation

5. Locate the exception data in the corresponding source table based on the exception table

SQL> select ID, name from T1 where rowid in (select row_id from exceptions );

ID name

------------------------------

1

1 B

3 D

3 E

6. Fix the abnormal data and make the index take effect.