Re: how can I get bind values for another session? There are conditions..

On Apr 11, 6:10 am, sybrandb <sybra..._at_gmail.com> wrote:
> On Apr 11, 11:09 am, salkaw..._at_yahoo.com wrote:
>
> > Hello,
>
> > I have a question about oracle10g. How can I get the bind variables?
>
> > Here is a description of the situation:
>
> > 1. A java process starts a stored procedure via jdbc.
> > 2. The procedure has a number of bind variables as input.
> > 3. The java process does NOT commit but just sleeps.
> > 4. I can't change the statistics_level (it must be typical).
> > 5. I cannot predict when this happens and I cannot trace all sessions
> > from that user due to performance requirements.
> > Can I use oradebug somehow?
>
> > Thanks for your help!
>
> Do you  really need to post this on every forum you can spell?
> Why don't you just:
>
> select * from dict where table_name like '%BIND%'  and read the docs?
>
> --
> Sybrand Bakker
> Senior Oracle DBA

On 9.2.0.6 Sybrand's query produced 7 hits: 3 dictionary views and 4 v $ views (2 gv$ and the 2 v$). None of these will expose another's sessions bind variables.

ALL_OPBINDINGS
All binding functions for operators available to the user

DBA_OPBINDINGS
All operator binding functiosn or methods

USER_OPBINDINGS
All binding functions or methods on operators defined by the user

GV$SQL_BIND_DATA
Synonym for GV_$SQL_BIND_DATA

GV$SQL_BIND_METADATA
Synonym for GV_$SQL_BIND_METADATA

V$SQL_BIND_DATA
Synonym for V_$SQL_BIND_DATA

V$SQL_BIND_METADATA
Synonym for V_$SQL_BIND_METADATA

The oradebug command set might be able to give you the information you want but I do not know how. Perhaps a process dump?

I suspect you will have to modify the code to capture and record (via an anonymous transaction) parameter information as desired based on some condition you set. Perhaps you add a debug parameter to the procedure or in the code check a trace on table for the module name and user and when found the routine records data.

One possible source of a problem: take a look at the error handling within the existing code and take a look at the code to see if NULL values being found for any data queried by the routine may not have been taken into consideration by the designer.

HTH -- Mark D Powell -- Received on Fri Apr 11 2008 - 08:30:11 CDT