Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> bind_data in v$sql

bind_data in v$sql

From: John Darrah <>
Date: Fri, 9 Feb 2007 13:05:11 -0700
Message-ID: <>

A while back I saw a post asking about the bind_data column in v$sql. The poster wanted to know how to interpret the RAW data in the column. At first I didn't see the point since there is also a V$sql_bind_capture view that has all the columns in plain text but I was curious about the structure of the column and started digging into it. I quickly stumbled on a procedure that translates this raw string called dbms_sqltune.extract_binds which made manually translating this data even less necessary but at that point I was having fun pulling the data out.
Here is what I have so far.

select /* unit_test_1_parse */ * from alpha where val = m_bind;

m_bind is defined as varchar2(1) := 'A'

Bind data string:
BEDA0A20020045C909F0000101F0012003690141 First part
BEDA0A200200 this is currently unknown but we do see patterns *BEDA0<--*A200200 was the same across all statements selected. It is also the same from AIX to linux and to BEDA0-->*A2002<--*00 appears to be related to the version of Oracle it was 2 in version and 3 in version, in other words A = 10 2 = 2 00 = 0 2 = 2
BEDA0A2002-->*00 *Separator??
BEDA0A200200-->*45C909F0<--*000101F0012003690141 this is the capture date: 45C909F0 = 1170803184 (in decimal) converts to "Tuesday, February 06, 2007 4:06:24 PM"
BEDA0A20020045C909F0-->*0001<--*01F0012003690141 this number is the total number of vars in the field (it could also be then number of total bind variables in the query regardless of whether or not they could fit in the RAW) *Note, this gives a hard limit of 64K is this the hard limit on binds altogether?
BEDA0A20020045C909F0000101F0-->*01<--*2003690141 data type BEDA0A20020045C909F0000101F001-->*20<--*03690141 this is the max length of the bind var 20 = 32 in decimal
BEDA0A20020045C909F0000101F00120-->*0369<--*0141* *this is the character set id hex for 873
BEDA0A20020045C909F0000101F001200369-->*0<--*141 unknown maybe a separator? BEDA0A20020045C909F0000101F0012003690-->*141 *the actual value 141 = "A"

The part I haven't been able to dissect is 01F0 I think the "0"s might be separators and the 1F is a bitmap of some kind (it changes depending on the data type) but that is only a guess.

Here is the information I know is in this RAW string because it it produced by the dbms_sqltune.extract_binds procedure:

For each bind the following information is present: NAME -- This appears not to be captured POSITION -- I don't think this is explicitly stored but determined from the total number of binds present in the string. DUP_POSITION -- don't know how this is stored DATATYPE *
Character Set ID *
VALUE * I've determined how the columns marked with a "*" are stored. Does anyone know how to interpret the rest of this string? Also, does anyone know if the binds that were peeked during a hard parse are stored anywhere? It appears that the bind_data string gets refreshed periodically by oracle, overwriting the originals.



Received on Fri Feb 09 2007 - 14:05:11 CST

Original text of this message