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

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);
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google