1

I am aware I can SELECT sql_text FROM v$sql to get the query but this is not the actual query being run since it still contains variables.

For instance it returns 

SELECT H.TYPE, H.STATUS, L.FLAG_2, L.QTY_ORDERED
FROM ODH H, ODL L
WHERE H.ORDER_NUMBER = L.ORDER_NUMBER
AND L.ORDER_NUMBER = :B2
AND L.ORDER_LINE = :B1
AND ROWNUM = 1

Is there a way to see the actual query that is being run so that I can determine what values being passed into the query?

sqluser
  • 5,502
  • 7
  • 36
  • 50
Steve Lloyd
  • 773
  • 2
  • 12
  • 33
  • enable session trace with bind capture, get the trace file from server - it will contain all the executed queries with bind values – Kirill Leontev Mar 03 '15 at 00:52
  • Trace the session with level 4 or 12, follow the steps as demonstrated here https://lalitkumarb.wordpress.com/2014/01/21/how-to-generate-trace-file-sql-trace-and-tkprof-in-oracle/ – Lalit Kumar B Mar 03 '15 at 05:53

1 Answers1

1
SELECT sql_id, value_string
FROM v$sql_bind_capture
WHERE name = ':B2'
OR name = ':B1'

OR

SELECT name, value_string
FROM v$sql_bind_capture
WHERE sql_id = your_query_id
sqluser
  • 5,502
  • 7
  • 36
  • 50
  • 2
    Just be aware that Oracle is not going to capture every set of bind values that is passed in. If you've got a SQL statement that works most of the time but occasionally generates unexpected results, it's unlikely that the bad bind values would be ones that Oracle happened to capture. You'd need to trace the session or use fine-grained auditing to get that information. – Justin Cave Mar 03 '15 at 00:44