Trace access errors in Oracle.

There are several possible causes for object access error in application:

• Application upgrade
• Deploying new rules for security
• Hardcoded schema names

Oracle common errors for those situations are:

ORA-00942: table or view does not exist
ORA-01031: insufficient privileges

Depends of application those errors can appear in application log files or only on user screen. Sometimes it is very difficult to find out a root cause of that kind of error – especially when it is out of the box application without proper logging and tracing possibility.
Oracle logging interface is very powerful and it allow a system administrator to setup a logging of above errors into Oracle alert.log file and into sessions trace files. To enable that functionality a trace level for error has to be increased – it can be done on system or session level. Of course for application a system level is more useful.
Following steps has to be performed on system or session level to trace a ORA-00942 error.

- for all sessions

alter system set events '942 trace name errorstack level 1';

for current session

alter session set events '942 trace name errorstack level 1'; 

Test user is executing following SQL statement:

SQL> select username from dba_users;
select username from dba_users
ERROR at line 1:
ORA-00942: table or view does not exist

Results in alter.log and trace files

Tue Sep 22 11:31:58 2009
Errors in file /oracle/app/diag/rdbms/pioro/pioro/trace/pioro_ora_23409.trc:
ORA-00942: table or view does not exist

Trace file details

Trace file details
*** 2009-09-22 11:31:58.607
*** SESSION ID:(152.1313) 2009-09-22 11:31:58.607
*** CLIENT ID:() 2009-09-22 11:31:58.607
*** SERVICE NAME:(SYS$USERS) 2009-09-22 11:31:58.607
*** MODULE NAME:(SQL*Plus) 2009-09-22 11:31:58.607
*** ACTION NAME:() 2009-09-22 11:31:58.607

----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=chvsmttqjzjkn) -----
select username from dba_users

Using above information from trace file DBA can figure out where a problem is and find a proper way to solve it.

regards
Marcin