ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle10g DML错误日志表

Oracle10g DML错误日志表

原创 Linux操作系统 作者:atlantisholic 时间:2011-04-27 18:11:36 0 删除 编辑

当insert 语句中使用子查询插入数据的时候(例如insert into a  select * from b),如果发生错误,insert语句会被中断,且整个事务会被回滚,如果子查询返回1000条,当在第501条时遇到错误。这个时候前面插入的500条数据都被回滚,既浪费时间,又浪费资源。下面将讨论如何避免这种情况的发生。

 

为了使用DML记录错误日志(DML错误日志表),需要在DML语句后面加一个语句,加上后DML引起的错误会被记录到一张特殊的表里(此表需要单独创建)。此时,DML引起的异常 将不会导致所有的操作全部回滚,oracle将略过错误,继续执行这个DML.

 

DML错误日志表对于insert ,update,merge,delete都起作用。这里以insert 为例。

 

Sql代码
  1. SQL> select * from t1;   
  2.   
  3.     DEPTNO DNAME                        LOC   
  4. ---------- ---------------------------- --------------------------   
  5.         40 develop                      China   
  6.         10 ACCOUNTING                   NEW YORK   
  7.         20 RESEARCH                     DALLAS   
  8.         30 SALES                        CHICAGO   
  9.         40 OPERATIONS                   BOSTON   
  10.   
  11. SQL> select * from t2;   
  12.   
  13.     DEPTNO DNAME                        LOC   
  14. ---------- ---------------------------- --------------------------   
  15.         40 develop                      China   
  16.         10 ACCOUNTING                   NEW YORK   
  17.         20 RESEARCH                     DALLAS   
  18.         30 SALES                        CHICAGO   
  19.   
  20. SQL> truncate table t2;   
  21.   
  22. 表被截断。   
  23.   
  24. SQL> select * from t2;   
  25.   
  26. 未选定行   
  27.   
  28. SQL> alter table t2 add constraint pk_t2 primary key (deptno);   
  29.   
  30. 表已更改。   
  31.   
  32. SQL> insert into t2 select * from t1;   
  33. insert into t2 select * from t1   
  34. *   
  35. 第 1 行出现错误:   
  36. ORA-00001: 违反唯一约束条件 (TEST.PK_T2)   
  37.   
  38.   
  39. SQL> select * from t2;   
  40.   
  41. 未选定行  

 

可见插入的时候由于主键不能重复,导致插入出现异常,现在一条数据也没插进去,呵呵,白费力气。

接下来讨论如何使用 LOG ERRORS 来避免这种情况:

 

1.建立DML错误日志表,利用oracle自带的包 里面有一个存储过程 可以拿来直接调用,含有2个参数,第一个:需要记录DML错误日志的表名(此例为T2),第二个:DML错误日志表的名字(此例为ERR_EMPL)。 

Sql代码
  1. SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('T2''ERR_EMPL');   
  2.   
  3. PL/SQL 过程已成功完成。  

     

2.插入数据

 

Sql代码
  1. SQL> insert into t2 select * from t1   
  2.   2  LOG ERRORS INTO err_empl ('t2_load') REJECT LIMIT 25;   
  3.   
  4. 已创建4行。   
  5.   
  6. SQL> select * from t2;   
  7.   
  8.     DEPTNO DNAME                        LOC   
  9. ---------- ---------------------------- --------------------------   
  10.         40 develop                      China   
  11.         10 ACCOUNTING                   NEW YORK   
  12.         20 RESEARCH                     DALLAS   
  13.         30 SALES                        CHICAGO  

 

可见数据插入成功了,只有主键重复的那一行记录没有插入成功。REJECT LIMIT 25的意思是如果错误数超过25 ,将中断此DML,回滚操作。

 

我们再来看看 记录错误日志的那张表里面都记录些什么:

Sql代码
  1. SQL> set linesize 500;   
  2. SQL> col DNAME format a10;   
  3. SQL> col DEPTNO format a10;   
  4. SQL> COL ORA_ERR_TAG$ format a5;   
  5. SQL> COL ORA_ERR_TAG$ format a10;   
  6. SQL> col LOC format a10;   
  7. SQL> col ORA_ERR_MESG$  format a20;   
  8. SQL> col ORA_ERR_ROWID$  format a10;   
  9. SQL> select * from err_empl   
  10. SQL> /   
  11.   
  12. ORA_ERR_NUMBER$ ORA_ERR_MESG$        ORA_ERR_RO ORA_ ORA_ERR_TA DEPTNO     DNAME      LOC   
  13. --------------- -------------------- ---------- ---- ---------- ---------- ---------- ----------   
  14.               1 ORA-00001: 违反唯一             I    t2_load    40         OPERATIONS BOSTON   
  15.                 约束条件 (TEST.PK_T2   
  16.                 )  

可以看出,此表记录了该DML执行 期间遇到的所有的错误。数据之所以在遇到错误后仍能够插入成功,归功于这张表。

 

下面是关于DML错误日志表column的一些描述:

 

Column Name Data Type Description

ORA_ERR_NUMBER$

NUMBER

Oracle error number

ORA_ERR_MESG$

VARCHAR2(2000)

Oracle error message text

ORA_ERR_ROWID$

ROWID

Rowid of the row in error (for update and delete)

ORA_ERR_OPTYP$

VARCHAR2(2)

Type of operation: insert (I), update (U), delete (D)

Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.

ORA_ERR_TAG$

VARCHAR2(2000)

Value of the tag supplied by the user in the error logging clause

 

上述column的含义很容易看懂吧。

 

记得这个实用的DML错误日志表,PS:此乃10G新特性,10G之前的版本不能使用。

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

请登录后发表评论 登录
全部评论

注册时间:2010-08-30

  • 博文量
    130
  • 访问量
    629268