SQL Error Logging

What is SQL Error Logging?

• Log error into database table, eliminate spool to OS file
• The logging table can be manually defined.

Demo

• Check Error Logging.

SQL> show errorlogging
errorlogging is OFF

• Enable Error Logging. A logging table is created under the current user.

SQL> set errorlogging on

SQL> show errorlogging
errorlogging is ON TABLE [SCHEMA].SPERRORLOG

• Describe the logging table

SQL> desc SPERRORLOG
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

• Try to create errors

-- Generate Error #1
SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
                 *
ERROR at line 1:
ORA-01476: divisor is equal to zero

-- Generate  Error #2
SQL> alter table EMP add new_column VARCHR2(1);
alter table EMP add new_column VARCHR2(1)
                                       *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

SQL> commit;

• Check the SQL Error Logging.

SQL> column username format A10
SQL> column message format A30 wrap
SQL> column statement format A30 wrap

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
                                           zero

TEST1_USER alter table EMP add new_column ORA-01735: invalid ALTER TABLE
            VARCHR2(1)                     option

• Set an identifier so it’s easier to locate your logs.

SQL> set errorlogging on identifier 'REL1'

• Generate Error #3.

SQL> alter table EMP modify ABC NOT NULL;
alter table EMP modify ABC NOT NULL
                       *
ERROR at line 1:
ORA-00904: "ABC": invalid identifier

• Check the SQL Error Logging with identifier='REL1‘

SQL> SELECT username, statement, message, IDENTIFIER FROM sperrorlog 
where IDENTIFIER='REL1';

USERNAME   STATEMENT                      MESSAGE                        IDENT
---------- ------------------------------ ------------------------------ -----
AUDIT_USER alter table EMP modify ABC NOT ORA-00904: "ABC": invalid iden REL1
            NULL                          tifier

• Truncate the Error Logging. All records will be gone.

SQL> set errorlogging on truncate

SQL> SELECT * FROM sperrorlog;

No rows selected

• Disable Error Logging. Log off will automatically disable it.

SQL> set errorlogging OFF

SQL> show errorlogging
errorlogging is OFF

See Also

http://oraexplorer.com/2011/03/oracle-11g-sql-error-logging/