3

How to aquire the full SQL statement with bind variables substituted from a trace file?

When setting

ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
ALTER SESSION SET sql_trace = true;

the resulting trace file contains the SQL query with bind variables and the resolution of the bind variables in a separate "BINDS" section. This is fine if there are a couple of bind variables. It's not very useful when I have 100+ bind variables.

Tkprof processes the trace file but does not support bind variables.

Is it possible to get the full SQL statements with the bind variables substituted so I can easily copy-paste and re-execute it? Is there maybe a free tool that will process my trace file and output the full SQL statements?

I'd also appreciate a solution without SQL tracing using v$sql and friends instead.

gabor
  • 1,030
  • 3
  • 12
  • 23
  • Yes, I know, 100+ bind variables is crazy, don't get me started on it. It's a giant table and the actual SQL is an insert statement generated by hibernate. – gabor Jul 06 '10 at 09:40

1 Answers1

3

You need to use Oracle trace analyzer instead of tkprof to extract actual values of bind variables. http://www.rampant-books.com/art_moore_oracle_trace_analyzer_trcanlzr_sql.htm

trace analyzer is going to replace eventually tkprof in future.

jsampath
  • 1,009
  • 1
  • 9
  • 10
  • I did not know trace analyzer. +1, as it's a nice tool and it gets me one step further, because it lists the actual values of the bind variables in a more accessible manner. But the bind variables are still in a separate section, not substituted into the SQL statement. That is, re-executing the SQL statement would require to substitute the bind variables by hand. – gabor Jul 06 '10 at 09:37