Finding SQL that is currently being executed is fairly trivial – all we need to know is SID of the current session which can be found in v$session dynamic performance view. However, we also need to lookup the values for the bind variables used in the retrieved SQL.
Let’s get the SQL string first:
SELECT b.* FROM v$session a JOIN v$sql b ON a.sql_id=b.sql_id WHERE a.sid=[#sid#]
If the actual SQL text is long, we may want to join v$sqltext view:
SELECT b.sql_text FROM v$session a JOIN v$sqltext b ON a.sql_id=b.sql_id WHERE a.sid=[#sid#] ORDER BY b.piece
So far so good, the only thing that may concern you at this moment is bind variables shown in the SQL.
You may need the actual values and not the variable names.
Here is the solution – we need to use v$sql_bind_capture view:
SELECT b.* FROM v$session a JOIN v$sql_bind_capture b ON a.sql_id=b.sql_id WHERE a.sid=[#sid#] ORDER BY child_number, position
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.