- Application upgrade
- Deploying new rules for security
- Hardcoded schema names
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
