Re: Vs: Re: Viewing bind variable values in 10g - Any suggestions?
- From: "Teijo Lallukka" <teijo.lallukka@xxxxxxxx>
- To: <knecht.stefan@xxxxxxxxx>
- Date: Thu, 06 Nov 2008 14:46:35 +0200
ok, thanks for all who responded...
I think that developer need to suffer with dumps it is still better than output
binds to screen from code...
I prefer SQL and would be nice to get SQL and binds with SQL after alter
session statement... but digging up dump from disk is not feasible for
developer...
I think auditing is not accurate enough to developer to get just that own
session which is going to debug.
Hopefully some day v$sql_bind_capture will do the trick...
-Teijo
>>> "Stefan Knecht" <knecht.stefan@xxxxxxxxx> 6.11.2008 14:00:18 >>>
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@xxxxxxxx>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@xxxxxxxxx> 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@xxxxxxxx
> >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
> >
> > --
> > //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@xxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxxxxxxx
http://www.opitz-consulting.ch
OCP 9i/10g SCSA SCNA
=========================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
