异常数据表Exceptions的使用--转-优快云博客

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(*)

----------

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_idexceptions 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功能，就可以方便的实现这种需要。

转自http://space.itpub.net/17203031/viewspace-735507

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15489979/viewspace-736307/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15489979/viewspace-736307/