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:[EMAIL PROTECTED]] 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: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
