DBMS_ERRLOG记录DML错误日志(一)

当一个DML运行的时候,如果遇到了错误,则这条语句会整个回滚,就好像没有执行过。不过对于一个大的DML而言,如果个别数据错误而导致整个语句的回滚,会浪费很多的资源和运行时间,从10g开始Oracle支持记录DML语句的错误,而允许语句自动继续执行。下面介绍一下DML记录语句的用法。

看一个插入语句的简单例子:

SQL> CREATE TABLE T1 AS SELECT ROWNUM A,ROWNUM B FROM DBA_SEGMENTS WHERE ROWNUM <=10;

Table created

SQL> CREATE TABLE T2 AS SELECT ROWNUM A,ROWNUM B FROM DBA_SEGMENTS WHERE ROWNUM <=20;

Table created

SQL> ALTER TABLE T1 ADD CONSTRAINT PK_T1_A PRIMARY KEY(A);

Table altered

SQL> INSERT INTO T1 SELECT * FROM T2;

INSERT INTO T1 SELECT * FROM T2

ORA-00001: 违反唯一约束条件 (NREI.PK_T1_A)

可以看到,由于插入的数据违反了唯一性约束,导致了Oracle报错。

下面创建记录DML错误信息的记录表,通过DBMS_ERRLOG包来进行创建,而这个包目前只包括这一个过程:

procedure create_error_log(dml_table_name
     varchar2,

                                 err_log_table_name  varchar2 default NULL,

                            
   err_log_table_owner varchar2 default NULL,

                            
   err_log_table_space varchar2 default NULL,

                            
   skip_unsupported    boolean  default FALSE);

利用CREATE_ERROR_LOG来创建T1表的DML错误记录表:

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','ERR_T1','NREI');

PL/SQL procedure successfully completed

DBMS_ERRLOG记录DML错误日志(一)

可以看到Oracle创建的错误记录表包括错误号码ORA_ERR_NUMBER$,错误信息ORA_ERR_MESG$,记录的ROWID信息ORA_ERR_ROWID$,错误操作类型ORA_ERR_OPTYP$,错误标签ORA_ERR_TAG$,以及表中对应的列。

下面利用包含LOG
ERROR语句的INSERT语句再次插入数据:

SQL>
INSERT INTO T1 SELECT * FROM T2 LOG ERRORS INTO ERR_T1('ERR_T1')REJECT LIMIT UNLIMITED;

10 rows inserted

DBMS_ERRLOG记录DML错误日志(一)

条。从对应的错误信息表中已经包含了插入的信息。而且从错误信息表中还可以看到对应的错误号和详细错误信息,ORA_ERR_OPTYP$为错误操作类型,I表示为Insert

关于LOG ERRORS的语法,INTO语句后面跟随的就是指定的错误记录表的表名。

在INTO语句后面,可以跟随一个表达式('ERR_T1')即是ORA_ERR_TAG$中存储的信息,用来设置本次语句执行的错误在错误记录表中对应的TAG。有了这个语句,就可以很轻易的在错误记录表中找到某次操作所对应的所有的错误,这对于错误记录表中包含了大量数据,且本次语句产生了多条错误信息的情况十分有帮助。只要这个表达式的值可以转化为字符串类型就可以。

而REJECT
LIMIT则限制语句出错的数量。

SQL> INSERT INTO T1 SELECT * FROM T2 LOG ERRORS INTO ERR_T1('ERR_T1')REJECT LIMIT 1;

INSERT INTO T1 SELECT * FROM T2 LOG ERRORS INTO ERR_T1('ERR_T1')REJECT LIMIT 1

ORA-00001: 违反唯一约束条件 (NREI.PK_T1_A)

可以看到,当设置的REJECT LIMIT的值小于出错记录数时,语句会报错,这时LOG ERRORS语句没有起到应有的作用,插入语句仍然以报错结束。而如果将REJECT LIMIT的限制设置大于等于出错的记录数,则插入语句就会执行成功。而所有出错的信息都会存储到LOG ERROR对应的表中。

只要指定了LOG ERRORS语句,不管最终插入语句十分成功的执行完成,在错误记录表中都会记录语句执行过程中遇到的错误。比如第一个插入由于出错数目超过REJECT LIMIT的限制,这时在记录表中会存在REJECT LIMIT + 1条记录数,因此这条记录错误导致了整个SQL语句的报错。

如果不管碰到多少错误,都希望语句能继续执行,则可以设置REJECT LIMIT为UNLIMITED

需要注意的是,即是做了回滚操作,ERR_T1表中的记录并不会减少,因为Oracle是利用自治事务的方式插入错误记录表的。

上一篇:(三)maven出错记录


下一篇:通过phoenix导入数据到hbase出错记录