Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: set_sql_trace_in_session

Re: set_sql_trace_in_session

From: Scott Shafer <>
Date: Mon, 19 Nov 2001 17:20:34 -0800
Message-ID: <>

How about for your current session:

ALTER SESSION SET timed_statistics=true; ALTER SESSION SET max_dump_file_size=unlimited; ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Or for another user's session:

SELECT s.username, p.spid

    FROM v$session s, v$process p
    WHERE s.paddr = p.addr

        AND s.username like 'LUSER%';

ALTER SYSTEM SET timed_statistics=true;
ALTER SYSTEM SET max_dump_file_size=unlimited; oradebug setospid <spid>
oradebug unlimit
oradebug event 10046 trace name context forever, level 8

Both straight from the pages (41-42) of "Oracle Performance Tuning 101" by Gaja Vaidyanatha and Kirti Deshpande (hope you guys don't mind - if you do just slap me down). Not sure about for a particular schema. Get this book and read chapter 2 over and over again. It doesn't matter how much it costs (not much) - its worth it! Check bookpool and amazon...

--Scott Shafer

   San Antonio, TX

> Hi Bruce,
> I thought of that but we have many VERY quick connects and disconnects
> application without persistent connections) so I'm not sure whether the
> would catch everything or what the overhead may be. I'll probably give it
> try anyway to see what comes out.
> Well I could do this:
> alter SYSTEM set events '10046 trace name context forever, level 8';
> but... it gives a lot of sys user stuff too. How to do this on a single
> schema?
> Anyone ever done this on a production system? It's connected to developing
> "real life" benchmarking routine.
> Steve Orr
> -----Original Message-----
> []
> Sent: Monday, November 19, 2001 4:36 PM
> To: ''
> Cc: Orr, Steve
> Steve,
> How about writing a PL SQL cursor to loop through all dedicated
> and then for each Sid, to use:
> Do a similar thing to turn it off.
> Bruce Reardon
> -----Original Message-----
> Sent: Tuesday, 20 November 2001 10:25
> On a similar note, I'm looking for a way to toggle trace system-wide.
> Changing the init.ora sql_trace parameter is not an option since it
> recycling the database.
> Any way to turn sql trace on for a few hours and then turn it back off?
> Steve Orr
> -----Original Message-----
> Sent: Monday, November 19, 2001 8:36 AM
> It is in the 8.1.6 documentation I have just looked at
> (Oracle8i Supplied PL/SQL Packages Reference
> Release 2 (8.1.6) A76936-01
> However I have a page on my website which shows how to put various bits of
> tracing on
> try for the home page or
> to go direct.
> John
> -----Original Message-----
> Sent: 19 November 2001 15:05
> DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION isn't documented in the 8.1.6/7
> doc sets. The package (and the procedure) exists, though even the
> package spec source code is wrapped. What gives? Is this package
> about to be desupported? Is there an alternative way of setting trace
> on in another session?

Do You Yahoo!?
Get your free address at
Please see the official ORACLE-L FAQ:
Author: Scott Shafer

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Nov 19 2001 - 19:20:34 CST

Original text of this message