Re: Vs: Re: Viewing bind variable values in 10g - Any suggestions?

Not at hand, no sorry.

The way you would go about setting this up is

• Turn on auditing for the database (set audit_trail = db_extended) -- you have to bounce the instance to change this parameter
• Turn on auditing for insert/update (or even select if you're interested in them) for the tables in question
• You will want to audit BY ACCESS - auditing by session will most likely kill your system's performance
• You can then use dba_audit_trail to find your sql's. But there's no sqlid in there, so you have to filter by sid, user or table to narrow them down

HTH Stefan

On Thu, Nov 6, 2008 at 12:40 PM, Teijo Lallukka <teijo.lallukka_at_edita.fi>wrote:

> Hi!
>
> Thanks for replies but...
>
> trace with level 4 is good way for me but developer face went green for
> too much work of digging dump... ;-)
>
> Do you have handy example how to get SQL + Binds from audit tables with one
> SQL_id ... ? or atleast which audit view those bind are going and how to
> query those nice way...
>
> -Teijo L.
>
> >>> "Stefan Knecht" <knecht.stefan_at_gmail.com> 6.11.2008 12:35:26 >>>
> There's several ways:
>
> - you can turn on auditing with db_extended. That will capture the bind
> values (along with the sql statement in aud$ table)
> - you can use 10046 trace at level 12 (to get wait + bind info in the
> tracefile)
>
> Stefan
>
> On Thu, Nov 6, 2008 at 10:58 AM, Teijo Lallukka <teijo.lallukka_at_edita.fi
> >wrote:
>
> > Hi list members!
> >
> > Do you know way to view bind variables which are used in insert/update
> > statement?
> >
> > I know that in 10g there is a view v$sql_bind_capture but it is very
> > limited and only usefull when bind is in where or having clause. And also
> > there is bug in timestamp datatype which will be fixed 11.2, I think.
> >
> > I would be thankfull for all ideas how to get binds from db without
> > touching application to get trace or debug info...
> >
> > -Teijo L.
> > Oracle DBA
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> =========================
>
> Stefan P Knecht
> Senior Consultant
> Systems Engineering
>
> OPITZ CONSULTING Schweiz GmbH
> Seestrasse 97
> CH-8800 Thalwil
>
> Mobile +41-79-571 36 27
> stefan.knecht_at_opitz-consulting.ch
> http://www.opitz-consulting.ch
>
> OCP 9i/10g SCSA SCNA
> =========================
>
>

-- 
=========================

Stefan P Knecht
Senior Consultant
Systems Engineering

OPITZ CONSULTING Schweiz GmbH
Seestrasse 97
CH-8800 Thalwil

Mobile +41-79-571 36 27
stefan.knecht_at_opitz-consulting.ch
http://www.opitz-consulting.ch

OCP 9i/10g SCSA SCNA
=========================

--
http://www.freelists.org/webpage/oracle-l