11g new feature - SQLPLUS error logging

Starting with Oracle 11g, there is very useful feature related to SQLPLUS. SQLPLUS is daily and mostly used tool by DBA, that is why I decided to write about it. In previous releases, in order to keep track of all the operations'(DDL/DML) errors we have to spool the output and then review the output from the spool file at the end. In 11g, we can simply turn on the error logging. Following that all the errors will be stored in special table called SPERRORLOG. By default, errorlogging will be off.

SQL> show errorlogging
errorlogging is OFF
SQL> set errorloggin on
SQL> show errorlogging
errorlogging is ON TABLE SYS.SPERRORLOG
SQL> selec * from dual;
SP2-0734: unknown command beginning  "selec * fr..." - rest of line ignored.
SQL> select * from du;
select * from du
*
ERROR at line  1:
ORA-00942: table or view does not exist

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

SQL>  select statement from sperrorlog;

STATEMENT
--------------------------------------------------------------------------------
selec  * from dual;
select * from du

SQL>

These records will be seen by all sessions. Question: what if I want to filter only for specific session?
Then we need to assign identifier when turning the errorlogging on.

SQL> set errorlogging ON identifier TIM
SQL> show errorlogging
errorlogging is ON TABLE SYS.SPERRORLOG  IDENTIFIER TIM
SQL> select y from dual;
select y from dual
*
ERROR at  line 1:
ORA-00904: "Y": invalid identifier

SQL> select  statement from sperrorlog;

STATEMENT
--------------------------------------------------------------------------------
selec  * from dual;
select * from du
select y from dual

SQL>  select statement from sperrorlog where identifier='TIM';

STATEMENT
--------------------------------------------------------------------------------
select  y from dual
SQL>

It is SQLPLUS feature, not database engine. I mean, you can connect with 11g SQLPLUS to lower release databases also.

Example:

[oracle@db11ghost admin]$ sqlplus sys@dbwin as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 14 18:15:50 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show errorlo
errorlogging is OFF

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

SQL> show errorlogging
errorlogging is ON TABLE SYS.SPERRORLOG

SQL> selec * from dual;
SP2-0734: unknown command beginning "selec * fr..." - rest of line ignored.

SQL> select statement from sperrorlog;

STATEMENT
--------------------------------------------------------------------------------
selec * from dual;

SQL>