Data check when Oracle restricts enable validate

1Create 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

2Create a tableEnable novalidatePrimary Key

SQL> alter table t1 add constraint pk_t1 primary key (id) enable novalidate;

Table altered

3Try to change the constraint statusEnable validateBut 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

4Find the data that violates the constraints. Here we useExceptionsAbnormal table saving 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

5Locate 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

6Fix abnormal data and make the index take effect.