Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: SQL TRACE

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Fri, 16 May 2003 10:40:47 -0800
Message-ID: <F001.0059B185.20030516104047@fatcity.com>


Tim, SQL_TRACE is 10046, level 1. You can check this with DBMS_SYSTEM.READ_EV.
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
Email:mgogala_at_oxhp.com

-----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, or DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, or 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 hamid.alavi_at_quovadx.com 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: http://www.orafaq.net -- Author: Tim Gorman INET: tim_at_sagelogix.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: MGogala_at_oxhp.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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 - 13:40:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US