Re: v$sql_bind_capture – is it buggy?
Date: Tue, 03 Jun 2008 09:55:56 -0700
> On Jun 2, 9:22 pm, zigzag..._at_yahoo.com wrote:
>> I am using Oracle 10.2.0.3 on Windows 2003. I am using >> V$sql_bind_capture to find values of bind vars used in my query. >> When I look at the description of v$sql_bind_capture: >> SQL> desc v$sql_bind_capture; >> Name Null? Type >> ----------------------------------------- -------- >> ---------------------------- >> ADDRESS RAW(4) >> HASH_VALUE NUMBER >> SQL_ID VARCHAR2(13) >> CHILD_ADDRESS RAW(4) >> CHILD_NUMBER NUMBER >> NAME VARCHAR2(30) >> POSITION NUMBER >> DUP_POSITION NUMBER >> DATATYPE NUMBER >> DATATYPE_STRING VARCHAR2(15) >> CHARACTER_SID NUMBER >> PRECISION NUMBER >> SCALE NUMBER >> MAX_LENGTH NUMBER >> WAS_CAPTURED VARCHAR2(3) >> LAST_CAPTURED DATE >> VALUE_STRING VARCHAR2(4000) >> VALUE_ANYDATA SYS.ANYDATA >> >> It has a column was_captured. My query has 206 bind vars, I see it >> only has value YES for this field for ~40 bind vars. Rest of the >> variables. Its value is NO, as a result VALUE_STRING is not shown >> (i.e., null). >> >> Why is not capturing all the bind variables. Where can I get values of >> all bind variables? I do not want to do sql trace with bind variables, >> I was hoping some v$ provides me all the values.
> No, it isn't. I would take the time to read up on the view rather
> than speculating on whether or not your lack of knowledge makes this
> object 'buggy' and waiting for someone to respond to your question:
> From the documentation:
> "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 also disabled when STATISTICS_LEVEL = BASIC.
> Funny how you have the time to wait for someone ELSE to read the
> documentation and report back to you.
> David Fitzjarrell
Worse than that. The only reason the posting was made here is that the OP didn't like the answers in the OTN forums where, I should point out, I pointed to the fact that any query with 206 bind variables has a much larger issue than whether the values are being capture.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Tue Jun 03 2008 - 11:55:56 CDT