Determine What Statement Fails in Someone Else's Session

By Edward Stoever

We just ran into a situation in which an automated process was failing because the user account it used had the DBA privilege revoked for security reasons. Something that it was programmed to do was causing the oracle error ORA-01031: insufficient privileges. I could see the errors accumulating in my error_log table, a table that is popluated by a system trigger that logs all system errors.

The question was... what SQL statement was causing the error??? To answer that, I needed to trace the session. The problem was that the user session was very brief, just a quick logon to run a few statements and then a logoff. I created a system trigger as SYS that turned tracing on and off for that user:

-- CREATE THIS TRIGGER AS SYS
-- or GRANT EXECUTE on SYS.DBMS_SYSTEM to trigger owner
CREATE OR REPLACE TRIGGER trace_trigger_on
   AFTER LOGON ON DATABASE
   WHEN (USER = 'WTAILOR')
DECLARE
   var_sid NUMBER;
   var_serial NUMBER;
BEGIN
   SELECT SID, serial#
     INTO var_sid, var_serial
     FROM v$session
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid;
   SYS.DBMS_SYSTEM.set_sql_trace_in_session (var_sid, var_serial, TRUE);
END;
/

-- CREATE THIS TRIGGER AS SYS
-- or GRANT EXECUTE on SYS.DBMS_SYSTEM to trigger owner
create or replace trigger trace_trigger_off
  BEFORE LOGOFF ON DATABASE
  when(user='WTAILOR')
  DECLARE
   var_sid NUMBER;
   var_serial NUMBER;
begin
   SELECT SID, serial#
     INTO var_sid, var_serial
     FROM v$session
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid;
 SYS.DBMS_SYSTEM.set_sql_trace_in_session (var_sid, var_serial, FALSE);
end;
/

Now, look in the USER_DUMP_DEST (the path can be found here:
           SQL>select value from v$parameter where name = 'user_dump_dest'; )

Here you will find the trace files that have accumulated. You will need to format these with the TKPROF program. To do so, just type TKPROF, the filename to format, and the output filename. For example:
        $ tkprof ALPHA_PROD_FG_ORACLE_042.trc TRACE_01_OUTPUT.txt

Now look at the TRACE_01_OUTPUT.txt file. My example is below.

TKPROF: Release 9.2.0.5.0 - Production on Fri Aug 20 15:45:13 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: DISK3:[ORACLE.V92.admin.prod.udump]ALPHA_PROD_FG_ORACLE_042.trc Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

insert into goreqer (goreqer_eqts_code, goreqer_eqnm_code, goreqer_seqno,
            goreqer_error_message, goreqer_user_id, goreqer_activity_date)
            values(:1, :2, gobrseq.NextVal, :3, USER, SYSDATE)

Error encountered: ORA-01031
********************************************************************************

There is the statement it is failing on!!! The user needs the select any sequence privilege!

CLEAN UP

It is a important to disable the triggers and turn off any tracing for current WTAILOR sessions:

SQL> alter trigger trace_trigger_on disable;
SQL> alter trigger trace_trigger_off disable;

By disabling the triggers, you have copies of them on the database for future use. But don't leave them enabled, because after a couple of months, you will accumulate a very large number of trace files!
   ---------
  Alternative:
  alter system set events '10046 trace name context forever, level 12';
  -- turns on tracing system wide at level 12 (captures values for
  -- bind variables and system waits).
  alter system set events '10046 trace name context off';
  -- turns off system wide tracing