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

On Apr 11, 9:30 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

PS - I forgot to mention that on 10g+ you could try looking at v $sql_bind_capture.

• Mark --