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