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: set_sql_trace_in_session

RE: set_sql_trace_in_session

From: Orr, Steve <sorr_at_rightnow.com>
Date: Tue, 20 Nov 2001 10:43:21 -0800
Message-ID: <F001.003C9ACA.20011120102120@fatcity.com>

For some reason I was shying away from the logon trigger approach but now I've reconsidered. Here's what I'm testing on:



CREATE OR REPLACE TRIGGER dbmon_logon
  AFTER LOGON ON DBMON.SCHEMA
BEGIN
  DBMS_SESSION.SET_SQL_TRACE (TRUE) ;
END ;
/

To toggle everything on & off:
alter trigger dbmon_logon enable/disable ;

Thanks all for your suggestions,
Steve Orr

-----Original Message-----

[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Monday, November 19, 2001 6:26 PM
To: Multiple recipients of list ORACLE-L

Steve,

What having a logon trigger that uses dbms_sql / exec immediate to do an alter session set sql_trace = TRUE
The logon trigger could have some intelligence to only do this for the schema you want.

I haven't ever done this on a production system for all sessions (but did get very close to doing it as a workaround for an 817 bug we encountered but that's another story).

Regards,
Bruce Reardon

-----Original Message-----

Sent: Tuesday, 20 November 2001 11:10

Hi Bruce,

I thought of that but we have many VERY quick connects and disconnects (web application without persistent connections) so I'm not sure whether the loop would catch everything or what the overhead may be. I'll probably give it a 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 a "real life" benchmarking routine.

Steve Orr

-----Original Message-----

[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Monday, November 19, 2001 4:36 PM

Steve,

How about writing a PL SQL cursor to loop through all dedicated connections and then for each Sid, to use:

   SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, TRUE);

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 requires 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 http://www.hcresources.co.uk for the home page or http://www.hcresources.f2s.com/trace.htm 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?

-- 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Orr, Steve
  INET: sorr_at_rightnow.com

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: 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 Tue Nov 20 2001 - 12:43:21 CST

Original text of this message

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