capture oracle bind_variable value_string null

bibi92
bibi92 used Ask the Experts™
on
Hello,

How can I capture bind variable for sql_id. I have tested the following script but the value_string is null and the sattistics level is typical.

select  sql_id,  t.sql_text SQL_TEXT,  b.name BIND_NAME,  b.value_string BIND_STRING
from  v$sql t  join DBA_HIST_SQLBIND b  using (sql_id)
where  b.value_string is not null  and sql_id='d4yj18kvu48j2'
/

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I've not used DBA_HIST_SQLBIND.

Whenever I've looked up bind variables I've used v$sql_bind_capture
Most Valuable Expert 2011
Top Expert 2012
Commented:
dba_hist_sqlbind isn't exhaustive.  It's only captures snapshots - as represented by the SNAP_ID column.

You can try to look in v$sql_bind_capture if you ran your query recently but even that isn't guaranteed.

There's even a column for that "WAS_CAPTURED" which, if NO, means it wasn't captured
Most Valuable Expert 2011
Top Expert 2012

Commented:
If you want to be sure to capture all binds ever used then trace your session with bind capture on then run your query and check the file.

obviously, this isn't something you'd have turned on all the time.
Most Valuable Expert 2011
Top Expert 2012

Commented:
A split is probably in order here.

slightwv also posted  v$sql_bind_capture, but before me

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial