From oracle-l-bounce@freelists.org Mon Apr 26 06:07:57 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3QB7g308853 for ; Mon, 26 Apr 2004 06:07:52 -0500 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 i3QB7W608838 for ; Mon, 26 Apr 2004 06:07:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 383D372CCA1; Mon, 26 Apr 2004 05:59:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29739-37; Mon, 26 Apr 2004 05:59:50 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6955B72CB96; Mon, 26 Apr 2004 05:59:49 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 26 Apr 2004 05:58:35 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F080472CBA3 for ; Mon, 26 Apr 2004 05:58:34 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29626-11 for ; Mon, 26 Apr 2004 05:58:34 -0500 (EST) Received: from lakermmtao12.cox.net (lakermmtao12.cox.net [68.230.240.27]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4958C72CC0C for ; Mon, 26 Apr 2004 05:58:34 -0500 (EST) Received: from smtp.east.cox.net ([172.18.52.53]) by lakermmtao12.cox.net (InterMail vM.6.01.03.02 201-2131-111-104-20040324) with SMTP id <20040426111055.RWMB164.lakermmtao12.cox.net@smtp.east.cox.net> for ; Mon, 26 Apr 2004 07:10:55 -0400 From: To: oracle-l@freelists.org, Subject: Re: Unable to do a 10046 trace on another users session Date: Mon, 26 Apr 2004 7:10:55 -0400 MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Message-Id: <20040426111055.RWMB164.lakermmtao12.cox.net@smtp.east.cox.net> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3734 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ryan.gaffuri@cox.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org by last line of the sql. do you mean just one sql statement? cary is right then you are dealing with many sessions. We are having this problem with connection pooling. The best way I found to handle it is to: 1. Make a user that has all the privileges of the username you want to trace. 2. Create a logon trigger to enable tracing when that user logs in. 3. Capture the full path of the trace file to a table. 4. write a korn shell script to tkprof all the trace files and concatenate them together (haven't had a chance to do this yet). There query is from ixora.com.au. Here is the trigger: CREATE OR REPLACE TRIGGER trig_trace_logon AFTER LOGON ON DATABASE BEGIN IF USER = 'TRACE' THEN INSERT INTO MYUSER.TRACE_LOG SELECT d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name, SYSDATE FROM ( select p.spid from sys.v_$mystat m, sys.v_$session s, sys.v_$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p, ( select t.instance from sys.v_$thread t, sys.v_$parameter v where v.name = 'thread' and ( v.value = 0 or t.thread# = to_number(v.value) ) ) i, ( select value from sys.v_$parameter where name = 'user_dump_dest' ) d; DBMS_SUPPORT.START_TRACE( waits=>true, binds=>true ); END IF; END; ---------------------------------------------------------------- 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 -----------------------------------------------------------------