V$SQL_BIND_CAPTURE

As of Oracle 10g, view V$SQL_BIND_CAPTURE is available, which stores the contents of bind variables for the executed SQL statements. On this basis, the bind variable contents specified for an SQL statement can be determined with the following query:

SELECT
SUBSTR(SBC.NAME, 1, 10) BIND,
SUBSTR(SBC.VALUE_STRING, 1, 50) VALUE,
COUNT(*) "NUMBER"
FROM
V$SQL_BIND_CAPTURE SBC, V$SQL S
WHERE
S.SQL_TEXT LIKE '' AND
S.SQL_ID = SBC.SQL_ID
GROUP BY NAME, VALUE_STRING
ORDER BY 1, 2;

The bind variable contents are refreshed in V$SQL_BIND_CAPTURE at the earliest every 15 minutes. If you require a faster refresh for analysis purposes, you can temporarily set the underlying underscore parameter _CURSOR_BIND_CAPTURE_INTERVAL to a value lower than 900 seconds (-> the default 15 minutes) as follows:

ALTER SYSTEM SET "_CURSOR_BIND_CAPTURE_INTERVAL"=; [@more@]