Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL TRACE


From: Tim Gorman <>
Date: Fri, 16 May 2003 11:46:46 -0800
Message-ID: <>

Ya know, I tried that (i.e. "alter system set events '10046 trace name context off'"), but it didn't work.

At least on the 81740 and 92010 instances I'm testing on -- maybe you've had different luck?

The AFTER LOGON triggers didn't prevent the trace files from being created, but they were all turned off after about 15Kb of output (from SQL*Plus startup). In the end, I found I had no alternative except to deal with it from the session level, and the AFTER LOGON trigger just made it easier...

on 5/16/03 11:40 AM, Gogala, Mladen at wrote:

> Tim, SQL_TRACE is 10046, level 1. You can check this with
> Your suggestion will take care of the session level. The system level would
> be to
> take the event off ("TRACE NAME CONTEXT OFF") at the instance level, what is
> generally
> done by using "ALTER SYSTEM".
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> -----Original Message-----
> Sent: Friday, May 16, 2003 1:57 PM
> To: Multiple recipients of list ORACLE-L
> If you are on 8i or above, then you can define an AFTER LOGON trigger ON
> DATABASE to perform "ALTER SESSION SET SQL_TRACE = FALSE" for every user
> session. That'll catch all newly-created sessions...
> For the background processes and already-running processes, you'll have to
> go in "manually" and disable SQL Trace using ORADEBUG, DBMS_SYSTEM.SET_EV,
> DBMS_SUPPORT.STOP_TRACE_IN_SESSION. Of course, it would be best to script
> something using "SQL-generating-SQL" in SQL*Plus...
> ---
> If you are on Oracle8 v8.0.x or below, then you don't have AFTER LOGON
> triggers (bummer!), but you still have ORADEBUG or DBMS_SYSTEM capabilities.
> You'll just be very busy "shooting down" all of the new sessions that way...
> :-)
> Hope this helps...
> on 5/16/03 9:32 AM, Hamid Alavi at wrote:

>> Hi List,
>> Is there any way to set the sql_trace=false whithout shutdown the

> database?
>> For all the sessions
>> ======================= Confidentiality Statement =======================
>> The information contained in this message and any attachments is
>> intended only for the use of the individual or entity to which it is
>> addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL
>> and exempt from disclosure under applicable law.  If you have received
>> this message in error, you are prohibited from copying, distributing, or
>> using the information.  Please contact the sender immediately by return
>> e-mail and delete the original message from your system.
>> ===================== End Confidentiality Statement =====================

Please see the official ORACLE-L FAQ:
Author: Tim Gorman

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
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 Fri May 16 2003 - 14:46:46 CDT

Original text of this message