How to Find Bind Variable Values of Oracle SQL Query

Oracle database captures a SQL query bind variables and their  values periodically, and the default capture bind variables interval is 900 seconds(15 minutes), which is modifiable but facing performance concerns.

Parameter Description                 Session Value  Instance Value
------------------------------------- -------------- --------------- 
_cursor_bind_capture_interval              900           900
interval(in seconds) between two bind 
capture for a cursor

SQL>alter system set "_cursor_bind_capture_interval"=30 scope=both;

Oracle intends to minimize the impact on database performance, and makes the capturing interval a pretty big value( like 15 minutes). So it is not a very reliable method to retrieve bind variables from Oracle database views.

Please note you need have licenses of “Oracle Diagnostics & Tuning Packs” if you query dba_hist_****** and v$sql_monitor in this post.

There are a couple of ways to retrieve bind variables in real time or from history.