One cool view is v$sql_bind_capture, this view hold the latest captured value for each bind variable in queries that has been run.
First have a look in v$sql to find the SQL query you are looking for, join the sql_id to v$sql_bind_capture and to view the bind variable values for that query.
Example:
selectI found a pretty bad example here, an Oracle internal dictionary query, but it should show the point.
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b
using (sql_id)
where
b.value_string is not null
and sql_id='f8pavn1bvsj7t'
/
SQL_TEXT BIND_NAME BIND_STRIN
------------------------------------------- ---------- ----------
select con#,obj#,rcon#,enabled,nvl(defer,0) :1 9110
from cdef$ where robj#=:1
10 comments:
just what i'm looking for
found you by googling: sql bind variable values
you were ranked # 4 in the search list
thanks man :)
Is there any way by whcih we can find out previous bind variable values, like for example I want to see bind variable passed to the query for it's run?
-Thanks,
Jayashree
try this one:
http://seilerwerks.wordpress.com/2008/01/29/vsql_bind_capture-not-quite-capturing-sql-binds/
you will a solution:
select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = …..
v$sql_bind_capture only captures the bind vars when the explain plan is created.
After executing the same select with a different bind value the new bind value does not appear in v$sql_bind_capture. It seems that Oracle uses this table only when it creates the explain plan and not when the explain plan already exists.
Execute this:
declare
my_var varchar2(10) := 'MYVAR_VAL1';
l_result number;
begin
select count(1)
into l_result
from dual
where dummy = my_var;
end;
And you can see the bind value using:
select * from v$sql_bind_capture where value_string like 'MYVAR_%'
Then execute the same PL/SQL block with my_var = 'MYVAR_VAL2' and check v$sql_bind_capture and you will see that the second value does not exist there.
Where can I find the bind var for all the queries even queries that were already hard parsed?
Hi, Thanks!
Is there anyway in Oracle 9i where I can see bind variable values?
- Anjul
Though I am replying to an old posting, but thought it may help someone. Try this:
select SQL_ID,LAST_CAPTURED,VALUE_STRING from DBA_HIST_SQLBIND where SQL_ID='0uf7cy3tdwpmf' order by 2;
Good Luck
Nirmal Arri
I was having some problems when I was working on pharmacy reviews site, and those problems are very similar to the ones you are describing on this post
Was Short and specific thanks
Really a excellent article!
it is what i was looking for, because when you do a explain plan with bind variables the output is different if you do with the exact values!
Thanks a lot for this great solution.
Can you please tell how to get bind values which are not in WHERE clause ?
Post a Comment