Company Knowledge base

Advanced Search
Home | General Database | Platform | Articles | Scripts | Online Documentation
Back

Articles
Date 2010-03-22 14:55:32
Component RAC
Title ERRORLOGGING feature
Version 11.1 >
Problem
Using SQLPLUS feature errorlogging.
 
With Oracle 11g sqlplus has a new features which can be used to store all errors resulting from executing sql, pl/sql and enabled to collect errors into a table. Also statements executed from scripts which result in an error will be stored in this table.
 
This is a “simple” but useful feature. Default the sperrorlog table is created, but that only counts for the sys user. You need to get access to the sperrorlog table of sys or have your own table created. The elgsetup.txt file in the $ORACLE_HOME/sqlplus/doc will show an example on how to create your own sperrorlog table.
Solution
The first time you enable sperrorlog the table will be automatically created, otherwise the table doesn’t exists.
 
 
$ sqlplus sys as sysdba
 
SQL*Plus: Release 11.1.0.7.0 - Production Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password: ******
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options
 
SQL> show errorlogging
errorlogging is OFF
 
SQL> descr sperrorlog
ERROR:
ORA-04043: object sperrorlog does not exist
 
 
SQL> set errorlogging on
SQL> descr sperrorlog
 SQL> descr 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> set errorlogging off
 
SQL> show errorlogging
errorlogging is OFF
 
As you can see in this example the sperrorlog table is created as soon as the errorlog is set for the first time. The table will not be dropped if you disable errorlogging.
 
 
If you use another user and perform the same step you will get an error message.
 
SQL> connect bcb
Enter password: ******
Connected.
 
SQL> show errorlogging
errorlogging is OFF
 
SQL> set errorlogging on
ERROR:
ORA-01031: insufficient privileges
ERROR:
ORA-00942: table or view does not exist
 
 
SP2-1518: Errorlogging table SPERRORLOG does not exist in schema BCB
 
 
SP2-1507: Errorlogging table, role or privilege is missing or not accessible
 
 
As you can see from the output we are not able to create our own table. Main case is we don’t have the proper privilege to create a table.
 
So to fix this we have 3 options.
 
1)        Get the correct privilege
2)        Set the errorlogging to the sys.sperrorlog table
3)        Create your own errorlog table based on $ORACLE_HOME/sqlplus/doc/ elgsetup.txt
 
 
Let’s take a closer look at the options.
 
Option 1: Get the correct privilege
 
SQL> grant dba to bcb;
 
Grant succeeded.
 
SQL> connect bcb
Enter password: ******
Connected.
SQL> show errorlogging
errorlogging is OFF
 
SQL> set errorlogging on
 
SQL> select count(*) from bcb.sperrorlog;
 
 COUNT(*)
----------
         0
 
 
Option 2: Set the errorlogging to the sys.sperrorlog table
 
 
SQL> connect sys as sysdba
Enter password: ******
Connected.
SQL> descr sperrorlog
 Name                                                              Null?    Type
 ----------------------------------------------------------------- --------
--------------------------------------------
 USERNAME                                                                   VARCHAR2(256)
 TIMESTAMP                                                                  TIMESTAMP(6)
 SCRIPT                                                                     VARCHAR2(1024)
 IDENTIFIER                                                                 VARCHAR2(256)
 MESSAGE                                                                    CLOB
 STATEMENT                                                                  CLOB
 
SQL> grant all on sperrorlog to bcb;
 
Grant succeeded.
 
SQL> connect bcb
Enter password: ******
Connected.
SQL> set errorloggin on table sys.sperrorlog;
 
SQL> show errorlogging
errorlogging is ON TABLE sys.sperrorlog
 
 
 
Option 3: Create your own sperrorlog table bases on the document in $ORACLE_HOME/sqlplus/doc/elgsetup.txt
 
 
SQL> create table bcb_sperrorlog(username   varchar(256),
                             timestamp TIMESTAMP,
                             script     varchar(1024),
                             identifier varchar(256),
                             message    CLOB,
                             statement CLOB);
 
Table created.
 
SQL> commit;
 
Commit complete.
 
SQL> set errorlogging on table bcb_sperrorlog
 
SQL> show errorloggi
errorlogging is ON TABLE SYS.bcb_sperrorlog
 

© RACHelp 2010 | About me | Disclaimers | Contact