ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 异常数据表Exceptions的使用

异常数据表Exceptions的使用

原创 Linux操作系统 作者:realkid4 时间:2012-07-15 13:58:06 0 删除 编辑

 

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  Copyright (c) 1991 by Oracle Corporation

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

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

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7545417