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.