Finding bind variable values

In 10g, you can use data dictionary view v$sql_bind_capture or AWR view dba_hist_sqlbind (historical version of v$sql_bind_capture) to find bind variables’ values. However it has some significant limitations:

  • Captured periodically (_cursor_bind_capture_interval=900 seconds by default), not at real time.
  • Captured under maximum size(_cursor_bind_capture_area_size=400)
  • Only bind variables in WHERE clause are captured (e.g bind variables passed to function are not captured !)

select name, position, datatype_string, value_string from v$sql_bind_capture where sql_id = '<sql_id>';

But there is a bug related to v$sql_bind_capture as reported in note 444551.1

V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP [ID 444551.1]

as a workaround you can get value of TIMESTAMP bind variable by

select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = '<sql_id>';

it’s fixed in 11.2.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: