In version 10, one doesn't need to use dbms_support (although the file to create it exists),
there is the following procedure:
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE)
If the application sets MODULE and ACTION fields by using DBMS_APPLICATION_INFO package,
one can trace all clients which execute that particular module automagically, by using the following:
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name IN VARCHAR2,
module_name IN VARCHAR2,
action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);
and there is a similar procedure for CLIENT_ID, setable from both DBMS_APPLICATION_INFO and
DBMS_SESSION. That is the very same client_id from V$SESSION that is also being used for
fine grain access control (FGAC). The only thing missing is the possibility to set up
trace identifier, so that the trace files could be easily distingusihed among by using "grep".
So, event 10046 is no longer necessary, the tracing mechanism can do that with the usual SQL_TRACE.
On 03/03/2004 09:59:55 AM, Cary Millsap wrote:
> You shouldn't have to turn tracing off to see a trace file. However, you
> do have to make sure that session (sid.serial#) emits at least one line
> of trace data; otherwise, the Oracle kernel won't have opened the trace
> file yet for writing.
>
> Note that with DBMS_SYSTEM.START_SQL_TRACE_IN_SESSION, you will not be
> able to activate *extended* SQL tracing (levels higher than 1). To get
> that, you'll have to use DBMS_SYSTEM.SET_EV(sid, serial#, 10046, level,
> ''), or--better yet--DBMS_SUPPORT.START_TRACE_IN_SESSION. It's
> confusing, I know. If you want to see either "bind" or "wait" data, then
> stay away from the package that has the word "SQL_TRACE" in the name.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)
> Sent: Wednesday, March 03, 2004 7:44 AM
> To: oracle-l@(protected)
> Subject: RE: question about dbms_syste.set_sql_trace_in_session
>
> Hi,
>
> run exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
>
> then check the trace dir.
>
> -----Original Message-----
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)
> Sent: Wednesday, March 03, 2004 6:35 PM
> To: oracle-l@(protected)
> Subject: question about dbms_syste.set_sql_trace_in_session
>
>
> i ran the following
>
> exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
>
> Procedure Completed Successfully
>
>
> I then went to my udump directory to check for a trace file and could
> not
> find one?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
