New SQLPLUS error Logging Facility

New error logging facility in 11g…spool errors from a table that traps them. Table is automatically created after using the SET command in SQLPLUS.

Data stored in the sperrorlog persists between sessions and whether it is interactive or non-interactive such as a script.

Should you turn on errorlogging for SYS?  Sort of a catchall auditing for errors not ordinarily trapped? I am turning on sperrorlog for SYS in a non-production 11gR2 database to see what happens.

New 11g SQLPLUS Parameter ERRORLOGGING [ID 471066.1]

SQL> select * from sperrorlog;
select * from sperrorlog
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> set errorlogging on;
SQL> select * from sperrorlog;

no rows selected

SQL > create public synonym mine for sys.holdingtable;

*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> select * from sperrorlog;

SQL> desc enduser.sperrorlog;
Name                                      Null?    Type
—————————————– ——– —————————-
USERNAME                                           VARCHAR2(256 CHAR)
TIMESTAMP                                          TIMESTAMP(6)
SCRIPT                                             VARCHAR2(1024 CHAR)
IDENTIFIER                                         VARCHAR2(256 CHAR)
MESSAGE                                            CLOB
STATEMENT                                          CLOB

ENDUSER
16-AUG-10 03.02.40.000000 PM
ORA-01031: insufficient privileges
create public synonym mine for sys.holdingtable

As per the documentation…you can setup and use a unique filename for the sperrorlog.

“Using User Defined Error Log Tables

To use a user defined log table, you must have permission to access the table, and you must issue the SET ERRORLOGGING command with the TABLE schema.tablename option to identify the error log table and the schema if applicable.”

http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch12040.htm#SQPUG160

DROP TABLE enduser_sperrorlog;

CREATE TABLE enduser_sperrorlog(username VARCHAR(256),

timestamp TIMESTAMP,

script VARCHAR(1024),

identifier VARCHAR(256),

message CLOB,

statement CLOB);

commit;

The enduser then issues the following SET command to enable error logging using the newly created error log table

SET ERRORLOGGING ON TABLE enduser_sperrorlog;