RE: Database tracing
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Sandeep Dubey" <dubey.sandeep@xxxxxxxxx>
- Date: Wed, 11 Jan 2006 13:13:41 -0700
Then use dbms_support to start traces on all of them:
Put the following into a file called trace.sql:
set verify off
declare
cursor c1 is select sid, serial# from v$session where osuser = '&1';
begin
dbms_output.put_line('Staring trace for the following sessions:');
for c1_rec in c1
loop
dbms_output.put_line('SID='||c1_rec.sid||', Serial#='||c1_rec.serial#);
dbms_support.start_trace_in_session(c1_rec.sid,c1_rec.serial#,waits=>false,binds=>true);
end loop;
end;
/
Then login and run @trace <osuser>
-----Original Message-----
From: Sandeep Dubey [mailto:dubey.sandeep@xxxxxxxxx]
Sent: Wednesday, January 11, 2006 1:01 PM
To: Allen, Brandon
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Database tracing
As I mentioned, when mulitple sessions (connection pools) are already
opened by application servers, it is impossible to know from which
session a sql is coming through.
Thanks
Sandeep
On 1/11/06, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
> Use oradebug, dbms_system or dbms_support (my favorite) to start a trace in
> an existing session, e.g.:
>
> conn / as sysdba
> @?/rdbms/admin/dbmssupp (if you haven't already executed this to create the
> dbms_support package)
> select sid, serial# from v$session where username = '<TARGET_USER>';
> execute
> dbms_support.start_trace_in_session(<sid>,<serial#>,waits=>true/false,binds=>true/false);
>
> Regards,
> Brandon
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sandeep Dubey
> Sent: Wednesday, January 11, 2006 12:26 PM
> To: Bobak, Mark
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Database tracing
>
>
> My mistake. Tracing is done for only new seesion that starts after
> enabling the trace. I was checking for some quries in the already open
> session. It works fine after I started a new session.
>
> A followup question - Is it possible to get trace for already opened
> session. If I have to trace for SQls comming through application
> server connection pooled session, which are already open when
> application server starts. Any ideas? Or I have restart the
> application server sessions to get trace.
>
> BTW- I think level 4 is the default.
>
> Thanks
>
>
>
> On 1/11/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote:
> > Don't forget to set the level:
> >
> > alter session set events '10046 trace name context forever, level x';
> >
> > Where x=1, standard SQL_TRACE=TRUE, no waits or binds
> > x=4, trace + binds
> > x=8, trace + waits
> > x=12, trace + binds + waits
> >
> > Your syntax for disabling trace looks fine to me.
> >
> >
> > -Mark
> >
> > --
> > Mark J. Bobak
> > Senior Oracle Architect
> > ProQuest Information & Learning
> >
> > "There are 10 types of people in the world: Those who understand
> > binary, and those who don't."
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandeep Dubey
> > Sent: Wednesday, January 11, 2006 1:37 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: Database tracing
> >
> > Hi,
> >
> > Database 10.2, OS Linux
> >
> > I used to get the whole database tracing using Alter system set events
> > '10046 trace name context forever', but now it doesn't seem to work.
> >
> > I am doing :
> >
> > SQL>Alter system set events '10046 trace name context forever';
> >
> > some sql queries
> >
> > SQL>Alter system set events '10046 trace name context off';
> >
> > I don't set any trace file in the user_dump_dest directory.
> > Timed_statistics is TRUE.
> >
> > Am I missing something?
> >
> > Thanks
> >
> > Sandeep
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
> >
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed by
> it.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
