SQL> desc sperrorlog;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT CLOB
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
SQL> show errorlogging ;
errorlogging is OFF
/* 默认情况下错误日志功能是关闭的，我们需要手动打开它 */
SQL> set errorlogging on;
SQL> show errorlogging ;
errorlogging is ON TABLE SYS.SPERRORLOG
/* 可以看到成功打开错误日志功能，并会将后续的错误日志写入到当前用户(SYS)名下的SPERRORLOG表 */
SQL> select 1 from abc;
select 1 from abc
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> set linesize 200;
SQL> col username for a20;
SQL> col message for a40
SQL> col statement for a40;
/* session A */
SQL> select 1 from abc;
select 1 from abc
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select username,message,statement from sperrorlog;
USERNAME MESSAGE STATEMENT
-------------------- ---------------------------------------- ----------------------------------------
SYS ORA-00942: table or view does not exist select 1 from abc
/* 此时在session B中查询 */
SQL> select username,message,statement from sperrorlog;
no rows selected
/* 换而言之当errorlogging被激活后，发生错误时Oracle会将错误日志追加到SPERRORLOG表上，但并不commit; */
/* 这可能导致V$LOCK动态性能视图中显示大量的TX锁，虽然是虚惊:) */
By setting 'ERRORLOGGING' parameter in SQLPlus, a new table 'SPERRORLOG' will be created. SQLPlus will write session errors to an error table SPERRORLOG. This feature very useful in order to track session errors. Example: SQL> set ERRORLOG ON SQL> desc SPERRORLOG Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) script VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB SQL> SELECT USERNAME, STATEMENT, MESSAGE, TIMESTAMP FROM SPERRORLOG; USERNAME STATEMENT MESSAGE ----------------------------------------------------------------- TIMESTAMP ----------------- SYS select * from table1 ORA-00942: table or view does not exist 16-DEC-07 02.21.43.000000 AM
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277579
