ORACLE使用EXCEPTIONS表查询出表中重复记录

SQL> conn system/oracle
Connected.
SQL> create table scott.t1(id number primary key,name varchar2(10)); --创建测试表

Table created.

SQL> select constraint_name,table_name,constraint_type, --查看相应完整性约束的状态
  2  status,deferrable,deferred,validated
  3  from dba_constraints
  4  where owner='SCOTT' and table_name='T1';

CONSTRAINT_NAME                TABLE_NAME                     C STATUS
------------------------------ ------------------------------ - --------
DEFERRABLE     DEFERRED  VALIDATED
-------------- --------- -------------
SYS_C0023319                   T1                             P ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED

SQL> insert into scott.t1 values(1,'tt');

1 row created.

SQL> insert into scott.t1 values(1,'hh');  --插入id列重复的记录失败
insert into scott.t1 values(1,'hh')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0023319) violated

SQL> alter table scott.t1 disable novalidate constraint SYS_C0023319;  --修改约束状态

Table altered.

SQL> select constraint_name,table_name,constraint_type,  --查看相应完整性约束的状态
  2  status,deferrable,deferred,validated
  3  from dba_constraints
  4  where owner='SCOTT' and table_name='T1';

CONSTRAINT_NAME                TABLE_NAME                     C STATUS
------------------------------ ------------------------------ - --------
DEFERRABLE     DEFERRED  VALIDATED
-------------- --------- -------------
SYS_C0023319                   T1                             P DISABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED

SQL> insert into scott.t1 values(1,'hh');  --插入id列重复数据成功

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.t1;

        ID NAME
---------- ----------
         1 tt
         1 hh

SQL> alter table scott.t1 enable validate constraint SYS_C0023319; --此时想把约束置为validate状态是不行的
alter table scott.t1 enable validate constraint SYS_C0023319
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.SYS_C0023319) - primary key violated

SQL> @utlexpt1  --此脚本在$ORACLE_HOME/RDBMS/ADMIN路径下

Table created.

SQL> desc exceptions  --查看exceptions表结构
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT                                         VARCHAR2(30)

SQL> alter table scott.t1 enable validate constraint SYS_C0023319  --再次执行该语句 并将错误记录到exceptions表中
  2  exceptions into system.exceptions;
alter table scott.t1 enable validate constraint SYS_C0023319
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.SYS_C0023319) - primary key violated

SQL> select rowid,id,name  --查看违反约束的数据
  2  from scott.t1
  3  where rowid in
  4  (
  5  select row_id
  6  from exceptions
  7  )
  8  ;

ROWID                      ID NAME
------------------ ---------- ----------
AAAR8jAAEAAAqs0AAA          1 tt
AAAR8jAAEAAAqs0AAB          1 hh

SQL> update scott.t1 set id=2 where name='hh';  --更改一条数据 使得表中数据符合约束

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table scott.t1 enable validate constraint SYS_C0023319; --再次执行该语句 则成功

Table altered.