bind capture - cheeyoung/sqlplus-public GitHub Wiki

V$SQL_BIND_(CAPTURE|DATA|METADATA)

query59.sql: query on v$sql_bind_capture and dba_hist_sqlbind

V$SQL_BIND_METADATA (11.2)

V$SQL_BIND_METADATA describes, for each distinct bind variable in each cursor owned by the session querying this view:

• Bind metadata provided by the client, if the bind variable is user defined
• Metadata based on the underlying literal, if the CURSOR_SHARING parameter is set to FORCE and the bind variable is system-generated.

V$SQL_BIND_CAPTURE (11.2)

V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. Each row in the view contains information for one bind variable defined in a cursor. This includes:

• Reference to the cursor defining the bind variable
  (hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor.
• Bind metadata
  Name, position, datatype, character set ID, precision, scale, and maximum length of the bind variable.
• Bind data
  One of the bind values used for the bind variable during a past execution of its associated SQL statement. Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement.

Bind capture is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC. This view can be joined with V$SQLAREA on (HASH_VALUE, ADDRESS) and with V$SQL on (HASH_VALUE, CHILD_ADDRESS).

21 Aug 2017 Created at 16:32