From oracle-l-bounce@freelists.org Wed Mar 3 09:36:00 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i23Fa0g03922 for ; Wed, 3 Mar 2004 09:36:00 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i23FZvo03909 for ; Wed, 3 Mar 2004 09:35:58 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7BCCF3950A4; Wed, 3 Mar 2004 10:36:07 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Mar 2004 10:34:54 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from smtp.wangtrading.com (smtp.wangtrading.com [167.206.68.5]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8C245394FBB for ; Wed, 3 Mar 2004 10:34:51 -0500 (EST) Received: from mladen.wangtrading.com (Not Verified[192.168.3.47]) by smtp.wangtrading.com with NetIQ MailMarshal (v5.5.5.8) id ; Wed, 03 Mar 2004 10:37:22 -0500 Received: from mladen (localhost.localdomain [127.0.0.1]) by mladen.wangtrading.com (8.12.8/8.12.8) with ESMTP id i23Fcf9B001909 for ; Wed, 3 Mar 2004 10:38:41 -0500 Date: Wed, 3 Mar 2004 10:38:41 -0500 From: Mladen Gogala To: oracle-l@freelists.org Subject: Re: question about dbms_syste.set_sql_trace_in_session Message-ID: <20040303153841.GA1871@mladen.wangtrading.com> References: <008f01c40130$32ff0bd0$945e8640@CVMLAP02> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Disposition: inline In-Reply-To: <008f01c40130$32ff0bd0$945e8640@CVMLAP02> (from cary.millsap@hotsos.com on Wed, Mar 03, 2004 at 09:59:55 -0500) X-Mailer: Balsa 2.0.16 Lines: 111 X-archive-position: 165 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mladen@wangtrading.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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@freelists.org > [mailto:oracle-l-bounce@freelists.org] On Behalf Of Senthil Kumar > Sent: Wednesday, March 03, 2004 7:44 AM > To: oracle-l@freelists.org > 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@freelists.org > [mailto:oracle-l-bounce@freelists.org]On Behalf Of ryan.gaffuri@cox.net > Sent: Wednesday, March 03, 2004 6:35 PM > To: oracle-l@freelists.org > 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@freelists.org > 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@freelists.org > 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@freelists.org > 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@freelists.org 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 -----------------------------------------------------------------