Oracle SQL trace with bind variables

sql oracle trace bind-variables
gabor picture gabor · Jul 5, 2010 · Viewed 8.1k times · Source

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.

Answer

jsampath picture jsampath · Jul 6, 2010

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.

Related questions

How to get tracing info for binding variables passed through OracleParameter in ODP.NET?

After Googling a lot and not finding what I'm looking for I decided to ask this question. I'm using binding variables as demonstrated in this awesome article from 2005 titled The Values That Bind by Mark A. Williams, like this: OracleParameter …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More