oracle 报错sql日志,oracle11g新特性-sqlplus错误日志 | 信春哥，系统稳，闭眼上线不回滚！...

可能大家都用过sqlplus的show error命令查看PL/SQL的错误信息，但是一条普通的查询操作报错，这个命令就查不到了。

SQL> select * from test;

select * from test

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> show error

没有错误。

11g开始引入sqlplus错误日志新特性，可以通过show errorlogging查看从无日志是否开启，默认是关闭状态。

SQL> show errorlog

errorlogging is OFF

可以通过set errorlogging on的方式开启sqlplus的错误日志。

SQL> set errorlogging on

SQL> show errorlog

errorlogging is ON TABLE SYS.SPERRORLOG

默认情况下，错误日志记录在当前用户下的SPERRORLOG表中(系统会自动创建SPERRORLOG表，如果当前用户下已经存在同名的SPERRORLOG表，则打开sqlplus错误日志时，不会创建SPERRORLOG表，如果已存在的SPERRORLOG表和sqlplus错误日志表的结构不一样，在执行报错错误的SQL时，将额外报SP2-1519错误)。

SQL> create table SPERRORLOG(id number,name varchar2(10));

表已创建。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

SPERRORLOG                     TABLE

SQL> set errorlogging on

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

SPERRORLOG                     TABLE

SQL> select * from test_1;

select * from test_1

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

ERROR:

ORA-00913: 值过多

SP2-1519: 无法写入错误日志表 STREAM.SPERRORLOG

在正确开启sqlplus的错误日志，那么我们执行一条SQL报错时，就可以在SPERRORLOG表中查到报错的记录。

SQL> select * from test_1;

select * from test_1

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> select USERNAME,TIMESTAMP,MESSAGE,STATEMENT from SPERRORLOG;

USERNAME TIMESTAMP                       MESSAGE                   STATEMENT

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

STREAM   20-3月 -13 03.00.22.000000 下午 ORA-00942: 表或视图不存在 select * from test_1

此时，在其他会话查询这个用户下的SPERRORLOG表不会查询到错误信息。

SQL> SELECT * FROM STREAM.SPERRORLOG;

未选定行

可见在SQL语句报错的时候，这个会话只是向SPERRORLOG表中插入了数据，但是并没有提交，那么怎样验证是这个会话没有只向SPERRORLOG表中插入记录报错的信息而没提交呢？也很简单，手动提交或者使用DDL语句隐式触发提交，其他会话就可以查询到这张表记录的错误信息了。

SQL> create table test_1(id number);

表已创建。

在其他会话就可以查询到stream用户下的SPERRORLOG表里面的内容了。

SQL> select USERNAME,TIMESTAMP,MESSAGE,STATEMENT from stream.SPERRORLOG;

USERNAME TIMESTAMP                         MESSAGE                        STATEMENT

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

STREAM   20-3月 -13 03.00.22.000000 下午   ORA-00942: 表或视图不存在      select * from test_1

在没有手动提交或隐式提交的情况下，这个会话断开与数据库的连接后，记录会自动回滚，可是在触发提交后，即使会话断开与数据库的连接，SPERRORLOG表中已提交的数据不会自动删除。

在打开sqlplus错误日志后，如果SQL报错，就会将报错信息记录到SPERRORLOG表中，然后还不提交，这样数据库中就会存在锁信息。

SQL> SELECT * FROM V$LOCK WHERE ID1=(SELECT OBJECT_ID FROM dba_objects where owner='STREAM' and object_name='SPERRORLOG');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

0E1EF30C 0E1EF33C        131 TM      77995          0          3          0       1051          0

当然，这个锁对数据库不会产生太大的影响，个人感觉，这个新特性对经常使用SQLPLUS的人来说可能有用，特别是习惯使用sqlplus书写PL/SQL的人，SPERRORLOG表中记录的PL/SQL错误信息要比 SHOW ERROR全面，但现在大部分人都习惯使用PLSQL Developer或其他第三方的工具来操作数据库，那么这个功能基本就没什么太大作用了。

本文也只是对11g的新特性-sqlplus的错误日志做简单的介绍，有兴趣的朋友可以深入研究下。