RE: dbms_system.set

From: Joe Smith <joe_dba_at_hotmail.com>
Date: Thu, 17 May 2007 12:30:06 -0500
Message-ID: <BAY133-F36659781DB9C947E9BA5997330@phx.gbl>

This is what I have done on a login trigger:

sys.dbms_system.set_ev(l_sid,l_serial#,10046,l_level,''); execute immediate 'alter session set tracefile_identifier = ''' || 10046 || '''';

and

sys.dbms_system.set_ev(l_sid,l_serial#,10053,l_level,''); execute immediate 'alter session set tracefile_identifier = ''' || 10053 || '''';

It works and generates 3 trace files for that user session:

ora_26282.trc
ora_26282_10046.trc
ora_26282_10053.trc

The 1st points to the 2nd and the 2nd points to the 3rd. I want to capture optimizer stats with the 10046 trace.

Does this make sense? The files seem to be smaller the usual.

thanks.

>Here's what I do
>
>accept tfid prompt 'Enter value for tracefile identifier: '
>alter session set current_schema= &&the_user;
>create or replace trigger &&the_user..trace_all
>after logon on schema
>begin
> execute immediate 'alter session set tracefile_identifier = ''&tfid''';
> DBMS_SUPPORT.START_TRACE;
>end;
>/
>
>
>Paul Baumgartel
>CREDIT SUISSE
>Information Technology
>Securities Processing Databases Americas
>One Madison Avenue
>New York, NY 10010
>USA
>Phone 212.538.1143
>paul.baumgartel_at_credit-suisse.com
>www.credit-suisse.com
>
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
>On Behalf Of Joe Smith
>Sent: Thursday, May 17, 2007 11:54 AM
>To: oracle-l_at_freelists.org
>Subject: dbms_system.set_ev
>
>Is there a way to name the trace file with dbms_system.set_ev();:
>
>sys.dbms_system.set_ev();
>
>I have a login trigger that does:
>
>sys.dbms_system.set_ev(l_sid,l_serial#,10046,l_level,'');
>
>but I need to name the trace file and can this be done with
>dbms_system.set_ev ?
>
>thanks.
>
>_________________________________________________________________
>Catch suspicious messages before you open them-with Windows Live Hotmail.
>http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_protection_0507
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>
>==============================================================================
>Please access the attached hyperlink for an important electronic
>communications disclaimer:
>
>http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>==============================================================================
>

Like the way Microsoft Office Outlook works? You’ll love Windows Live Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_outlook_0507

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

Received on Thu May 17 2007 - 12:30:06 CDT

RE: dbms_system.set_ev