Friday, May 10, 2013

Finding Bind variables in SQL's executed...

v$sql_bind_capture --> this view shows the bind variables passed to the sql at the run time
DBA_HIST_SQLBIND --> historical information

Ex:

select b.last_captured, a.sql_text, b.name, b.position, b.datatype_string, b.value_string
  from dba_hist_sqlbind b,
       --gv$sql_bind_capture b,
       v$sqlarea a
 where b.sql_id = '66j3kkqnv3m2f' --> sqlid that you are interested in looking for bind variable passed.
   and b.sql_id = a.sql_id

From Oracle Docs on DBA_HIST_SQLBIND:

DBA_HIST_SQLBIND displays historical information on bind variables used by SQL cursors. This view contains snapshots of V$SQL_BIND_CAPTURE.
LAST_CAPTUREDDATE Date when the bind value was captured. Bind values are captured when SQL statements are executed. To limit the overhead, binds are captured at most every 15 minutes for a given cursor.

No comments:

Post a Comment