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

16-AUG-10 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.”

DROP TABLE enduser_sperrorlog;

CREATE TABLE enduser_sperrorlog(username VARCHAR(256),

timestamp TIMESTAMP,

script VARCHAR(1024),

identifier VARCHAR(256),

message CLOB,

statement CLOB);


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;


About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in 11g, errors. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s