Constraint約束是我們進行資料庫設計、管理的一個重要方面。在日常工作中，充分利用Constraint來描述我們的資料，可以幫助我們在資料層構建起約束關係，保護資料完整性。同時，在一些特殊的場景下，完整的約束還會幫助我們生成更好的執行計劃。

在生產環境下，我們進行一些資料操作時候，也會進行約束的管理。例如我們為了加快資料匯入載入速度，可能會暫時的將主鍵、外來鍵和索引等約束禁用掉。操作之後，重新啟用。

這種時候，我們會遇到一些例外情況，一些匯入的資料可能並不滿足約束要求，引發問題。此時，我們就需要發現這些異常資料。

如果這些資料量比較小，我們可以較容易的發現錯誤資料行記錄。但是如果資料量很大，那麼這就是一個很費功夫的工作。

在Oracle中，我們可以啟用Exceptions資料表功能。當我們啟用約束的失敗的時候，Oracle可以將那些引起失敗的資料行記錄儲存在其中。

1、環境準備

我們選擇構建資料表T，實驗環境是Oracle 11gR2。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

SQL> create table t as select * from dba_objects where 1=0;

Table created

構建主鍵約束pk_t_id。

SQL> alter table t add constraint pk_t_id primary key (object_id);

Table altered

由於需要大規模載入資料，暫時性的disable掉約束。之後載入髒資料。

SQL> alter table t disable constraint pk_t_id;

Table altered

SQL> insert into t select * from dba_objects;

72460 rows inserted

此時，啟用主鍵出錯。

SQL> alter table t enable constraint pk_t_id;

alter table t enable constraint pk_t_id

ORA-02437: 無法驗證 (SYS.PK_T_ID) - 違反主鍵

現在，我們希望知道那些資料是違反主鍵約束的。可能是重複，也可能是主鍵列出現空置。

2、初始化Exceptions資料表

在預設情況下，Oracle是不會安裝Exceptions資料表的。如果我們需要使用，需要手工的進行安裝建立。

安裝Exceptions是透過Oracle Home目錄下的一個指令碼。我們可以透過呼叫伺服器端的指令碼實現。

[oracle@bspdev admin]$ pwd

/u01/app/oracle/rdbms/admin

[oracle@bspdev admin]$ env | grep ORACLE_HOME

ORACLE_HOME=/u01/app/oracle

[oracle@bspdev admin]$ ls -l | grep exc

-rw-r--r--. 1 oracle oinstall 705 Sep 3 1997 utlexcpt.sql

[oracle@bspdev admin]$ quit

-bash: quit: command not found

[oracle@bspdev admin]$ cat utlexcpt.sql

rem

rem $Header: utlexcpt.sql,v 1.1 1992/10/20 11:57:02 GLUMPKIN Stab $

rem

Rem NAME

Rem except.sql -

Rem DESCRIPTION

Rem

Rem RETURNS

Rem

Rem NOTES

Rem

Rem MODIFIED (MM/DD/YY)

Rem glumpkin 10/20/92 - Renamed from EXCEPT.SQL

Rem epeeler 07/22/91 - add comma

Rem epeeler 04/30/91 - Creation

create table exceptions(row_id rowid,

owner varchar2(30),

table_name varchar2(30),

constraint varchar2(30));

執行指令碼，並且為了實現exceptions共享，可以建立公共同義詞。

[oracle@bspdev admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 13 13:54:17 2012

SQL> conn / as sysdba

Connected.

SQL> @?/rdbms/admin/utlexcpt.sql

Table created.

SQL> create public synonym exceptions for exceptions;

Synonym created.

SQL> desc exceptions;

Name Type Nullable Default Comments

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

ROW_ID ROWID Y

OWNER VARCHAR2(30) Y

TABLE_NAME VARCHAR2(30) Y

CONSTRAINT VARCHAR2(30) Y

3、使用exceptions資料表容納錯誤資訊

資料表中存在幾條null object_id記錄，這些顯然是影響主鍵生效的因素。

SQL> select count(*) from t where object_id is null;

COUNT(*)

----------

4

SQL> select rowid from t where object_id is null;

ROWID

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

AAAaqZAABAAAWEEAAk

AAAaqZAABAAAWEEAAl

AAAaqZAABAAAWEEAAm

AAAaqZAABAAAWEEAAn

使用exceptions into exceptions語句，可以將違反約束的記錄資訊記錄到資料表中。

SQL> alter table t enable constraint pk_t_id exceptions into exceptions;

alter table t enable constraint pk_t_id exceptions into exceptions

ORA-02437: 無法驗證 (SYS.PK_T_ID) - 違反主鍵

SQL> col owner for a10;

SQL> col table_name for a15;

SQL> select * from exceptions;

ROW_ID OWNER TABLE_NAME CONSTRAINT

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

AAAaqZAABAAAWEEAAk SYS T PK_T_ID

AAAaqZAABAAAWEEAAl SYS T PK_T_ID

AAAaqZAABAAAWEEAAm SYS T PK_T_ID

AAAaqZAABAAAWEEAAn SYS T PK_T_ID

違反約束的rowid相同，正確返回結果。

4、結論

當我們啟用大資料表時，很多時候是需要知道違反記錄的資料行，之後進行調整清洗。藉助exceptions功能，就可以方便的實現這種需要。

異常資料表Exceptions的使用

相關文章