среда, 4 июня 2008 г. в 05:00:11 UTC+3, zigz...@yahoo.com:

On Jun 3, 6:21 pm, Yong Huang <yong...@yahoo.com> wrote:

On Jun 2, 9:22 pm, zigzag...@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:

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.

Hi,

In addition to the limitation listed in the Reference manual, bind
data capture is controlled by other factors. Either statistics_level
needs to be typical (or all) or _cursor_bind_capture_area_size has to
be manually set to non-zero (it becomes zero if you lower statistics_level). If it's not the case no values are captured at all,
it's also possible _cursor_bind_capture_area_size needs to be
increased. Bind data capture happens during a cursor hard parse, a
soft parse that creates a new child cursor, or if the last capture was _cursor_bind_capture_interval seconds or longer ago, and bind
variables in the select list are ignored. There may be other factors I haven't figured out that cause the bind data capture to not happen.

Yong Huang- Hide quoted text -

- Show quoted text -

Young:
Most useful answer. Thanks.

man, I'm writing this from 2022 and I have to admit I've never seen bigger bunch of toxic egobabies anywhere in the internet. Like...Ppl just can't make themselves to pass by when all they need to do is to answer a simple question, jeez. They just gotta
be a bunch of pricks.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)