v$sql_bind_capture Not Quite Capturing SQL Binds
A week or so ago, I was working with a developer to find out why a SQL statement he was sending would sometimes work and sometimes return an invalid DATE format error. Part of the troubleshooting led us to examine the actual bind values being received by the server, just to make sure it was the same as being sent. I thought I was onto something when I saw that the fields of TIMESTAMP datatype actually were NULL:
SQL> select name, datatype_string, value_string
from v$sql_bind_capture
where sql_id='0wp5c2a3z82jr'
SQL> /
NAME DATATYPE_STRING VALUE_STRING
---- --------------- ------------
:1 TIMESTAMP {NULL}
:2 VARCHAR2(32) 3WC
:3 VARCHAR2(32) 001
:4 TIMESTAMP {NULL}
:5 NUMBER 8429721
:6 NUMBER 206
6 rows selected.
Unfortunately it was like this when the query both failed and succeeded. I banged my head against the wall for 30 more minutes before concluding that it must be a bug. Metalink agreed, and Note 444551.1 details the bug, which is not yet fixed and “should be fixed in 11.2,” affecting versions 9.2 to 11.1. Seems kind of long for something like this to still be out in the open, and it’s very annoying when you really want to see the value of a TIMESTAMP bind variable.
Don,
try this workaround:
select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = …..
rgds
ghassan
January 30, 2008 at 2:55 pm
[...] Don Seiler found a bug with v$sql_bind_capture. [...]
Links of the Week - #4 « I’m just a simple DBA on a complex production system
February 1, 2008 at 12:51 pm
[...] di tipo date della insert non comparivano, mentre per le altre si. Mi vi è venuto quindi in mente un post di Don Seiler, in cui pare l’autore spiega come si trovasse ad analizzare un problema non molto diverso dal [...]
V$SQL_BIND_CAPTURE e TRACE 10046, stesso baco? « Oracle and other
February 7, 2008 at 6:00 am
Hi Don,
it seems that same bug involves also trace of event 10046. If you enable session tracing (for example with dbms_monitor.session_trace_enable) than in tracefile bind variable values for timestamps are absent. That is also more annoying.
cristiancudizio
February 7, 2008 at 6:08 am
Hi,
I have been working on an Oracle DBA/Sysadmin Dashboard. What do you think are the key ingredients of a DBA dashboard?
Your opinion is highly regarded
Regards
Nilesh
Dashboards
Dashboards
February 12, 2008 at 7:33 pm
Don,
Here’s a link to the solution for syntax highlighting in WordPress.
Tyler
Tyler Muth
February 22, 2008 at 10:18 am
Thanks Tyler!
Don Seiler
February 22, 2008 at 3:44 pm