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
SQL> show errorlogging errorlogging is OFF
 * Check Error Logging.

SQL> set errorlogging on
 * Enable Error Logging. A logging table is created under the current user.

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

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

-- 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
 * Try to create errors

-- 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;

SQL> column username format A10 SQL> column message format A30 wrap SQL> column statement format A30 wrap
 * Check the SQL Error Logging.

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

SQL> set errorlogging on identifier 'REL1'
 * Set an identifier so it’s easier to locate your logs.

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

SQL> SELECT username, statement, message, IDENTIFIER FROM sperrorlog where IDENTIFIER='REL1';
 * Check the SQL Error Logging with identifier='REL1‘

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

SQL> set errorlogging on truncate
 * Truncate the Error Logging. All records will be gone.

SQL> SELECT * FROM sperrorlog;

No rows selected

SQL> set errorlogging OFF
 * Disable Error Logging. Log off will automatically disable it.

SQL> show errorlogging errorlogging is OFF