How to Find Bind Variable Values for SQL Currently Executed by Oracle Server, by Zahar Hilkevich

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s