2

I have an oracle query generating a 3137 error, and the trace file contains the query. For example: 

select x from y where z = :1

I think it would help to isolate the problem if I could determine the specific value of :1, however the 4.7M file doesn't seem to offer that information.

Is this information stored in the trc file?

This is oracle 11g running on redhat.

Nathan Feger
  • 151
  • 8

3 Answers3

1

Not sure if the .trc file described here is made the same way yours was, but this might help you.

Essentially, somewhere in there, there should be a section of Binds which shows which values were bound to that :1 variable. The article doesn't do a good job of explaining how to tell which binds went to which queries since the example had only one query (and I can't figure it out from looking at it). Ignore the sort|uniq bit they used at the end to show that it was always the same query over and over, and you should get results like

Bind#0
  value=55

or whatever.

DerfK
  • 19,493
  • 2
  • 38
  • 54
  • 1
    The tie in between a statement and the binds is the cursor number. In the link description the statement is seen in PARSING IN CURSOR #2 and the binds in BINDS #2. But the same cursor number may get reused by the session as cursors are closed and opened. – Gary Jan 11 '11 at 22:31
  • @Gary thanks for the cursor hint, I do see many cursor notes, but am still not able to tie them to a bind variable. – Nathan Feger Jan 12 '11 at 14:27
1

Worth checking out the Trace Analyzer if you have an Oracle support contract.

Gary
  • 1,839
  • 10
  • 14
0

Source of the problem

If the trace (.trc) file is missing Bind Variable values (or Parameter values in a OracleCommand in ODP.NET for example) this is due to the way the trace has been started.

If you use one of the following commands as the Using Application Tracing Tools page states:

ALTER SESSION SET SQL_TRACE = TRUE;

EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

..you are not recording bind values!

Solution

To record bind variable values there are other, alternative ways, to start the trace depending on your Database version and loaded packages. If you are on Oracle 10g+ the easiest way is to use the following command to start the trace:

DBMS_MONITOR.session_trace_enable(waits=>FALSE, binds=>TRUE)

Here is the complete list of available commands to start the trace with bind variables recording (source: SQL trace, 10046, trcsess and tkprof in Oracle 10g):

-- All versions.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>4, nm=>' ');

-- All versions, requires DBMS_SUPPORT package to be loaded.
EXEC DBMS_SUPPORT.start_trace(waits=>FALSE, binds=>TRUE);
EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>FALSE, binds=>TRUE);

-- Oracle 10g
EXEC DBMS_MONITOR.session_trace_enable(waits=>FALSE, binds=>TRUE);

EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, binds=>TRUE, binds=>TRUE);

EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>FALSE, binds=>TRUE);

EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running', -
> waits=>FALSE, binds=>TRUE);
Fulvio
  • 101
  • 1
  • I know this is an old question, but it was still unanswered, and I just had the same problem: I had the trace file, but it was missing _Bind#whatever_ information (as per Nathan Feger question). After some googling I finally found the solution, and thought it was good to answer the question, in the hope it helps someone having the same problem. – Fulvio May 28 '12 at 10:17