Oracle 11g Auditing Failed Statements

To find out what queries cause ORA-00942 - table or view does not exist errors you can use the auditing features of Oracle. You can audit successful as well as unsuccessful attempt to run particular queries against the Oracle database.

audit select table by access whenever not successful; -- also insert table, delete table, update table

To read the audit trail -

select * from dba_audit_trail

To make sure auditing is on do -
SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>
Auditing is disabled by default,
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

as SYSDBA
alter system set audit_trail = 'db_extended' scope=pfile; -- or none, os, db, xml, xml_extended

db_extended audits sql_text and bind values;

SHUTDOWN
STARTUP


Otherwise do this from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2975793633621#284628100346267414

create table t( id number, seq number, msg varchar2(4000) ); create sequence s; create or replace trigger failed_to_extend_temp after servererror on database declare l_sql_text ora_name_list_t; l_n number; begin if ( is_servererror(942) ) then insert into t values ( s.nextval, 1, 'ora_sysevent = ' || ora_sysevent ); insert into t values ( s.currval, 2, 'ora_login_user = ' || ora_login_user ); insert into t values ( s.currval, 3, 'ora_server_error = ' || ora_server_error(1) ); l_n := ora_sql_txt( l_sql_text ); for i in 1 .. l_n loop insert into t values ( s.currval, 3+i, 'l_sql_text(' || i || ') = ' || l_sql_text(i) ); end loop; end if; end;