Oracle 11g New Feature : SQLPlus Error Logging Features and advantages

advertisements

_____________________________________________________________________________________________________________________

In oracle 11g one new useful feature is added with SQL*Plus which is commonly used by the DBAs. Prior to oracle 11g we need to spool the sqlplus logs to keep track of the error outputs. But here is the advantage that we can simply turn on the ERRORLOGGING with SQL*Plus which will internally log all the errors into SPERRORLOG table.
By default the ERRORLOGGING is off. It is a 11g SQL*Plus feature not with database engine.

Once you set ERRORLOGGING on for a particular session the SPERRORLOG table creates under that user.

SQL> connect / as sysdba;

Connected.

SQL> select object_name, owner from all_objects where object_name ='SPERRORLOG';

OBJECT_NAME                    OWNER

------------------------------ ------------------------------

SPERRORLOG                     SCOTT

In the below example SPERRORLOG table is not present for sys user and below I am going to set ERRORLOGGING ON. Once you set the errorlogging the table  SPERRORLOG gets created for that particular user.

SQL> set errorlogging on

SQL> select object_name, owner from all_objects where object_name ='SPERRORLOG';

OBJECT_NAME                    OWNER

------------------------------ ------------------------------

SPERRORLOG                     SYS

SPERRORLOG                     SCOTT

SQL> show user

USER is "SYS"

You can create your own error log tables for your convenience. See the example below

SQL> create table errorlog as select * from SPERRORLOG where 1=2;

Table created.

SQL> set errorlogging on table errorlog;

SQL> select 'x from dual;

ERROR:

ORA-01756: quoted string not properly terminated

SQL> select statement from errorlog;

STATEMENT

--------------------------------------------------------------------------------

select "x from dual;

Or you can create the table with following syntax.

CREATE TABLE errorlog(username VARCHAR(256),

timestamp TIMESTAMP,

script VARCHAR(1024),

identifier VARCHAR(256),

message CLOB,

statement CLOB);

_____________________________________________________________________________________________________________________

Website Stats