Wednesday, December 2, 2009

Oracle 10g : View bind variables values without tracing

In 10g there is a new view called: V$SQL_BIND_CAPTURE

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 such as :

1) Reference to the cursor defining the bind variable
(hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor.
2) Bind metadata
Name, position, datatype, character set ID, precision, scale, and maximum length of the bind variable.

3) 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( This excludes update and insert statements).

You can query this view using sql_id or hash_value for an sql statement.

e.g

1) Find has_value for your statement

select sql_text,sql_id,hash_value from v$sqlarea
where lower(sql_text) like '%[my statement]%';

2) FInd bind variables values

SELECT name,datatype_string,was_captured,last_captured,value_string,value_anydata
FROM v$sql_bind_capture
WHERE hash_value='[hash_value_from_previous_sql]';

No comments:

Post a Comment