Unable to disable tracing - XE
in [Oracle]
|
Prev: what's the internal transaction table for....
Next: dbms_session.set_context from JDBC fails mysteriously, how can I trap it ?
From: BD on 9 Feb 2010 20:01 .... or at least I would be if I had any. I am running a copy of 10G XE on a WinXP box. In a troubleshooting session some time ago, I turned on some additional tracing - and quite simply have been unable to turn it off. Below is an extract from one of my .trc files in my bdump directory. It's the alter session statements in this trace that I want to disable - they're resulting in way more trace files than I need now. I've tried ALTER SYSTEM statements, ALTER SESSION statements, to try to disable these statements, but they keep getting asserted again on instance restart. I'm sure I'll just smack myself in the head once I work this out, but can someone point me to where I can disable these statements? Given that it's XE, it'd probably be just as time-effective to reinstall as to dial this in... but still. Thanks kindly!! *** SERVICE NAME:(SYS$USERS) 2010-02-02 09:46:36.593 *** SESSION ID:(31.46) 2010-02-02 09:46:36.593 ===================== PARSING IN CURSOR #6 len=69 dep=2 uid=0 oct=42 lid=0 tim=80934443123 hv=3164292706 ad='6688fd70' alter session set events '10046 trace name context forever, level 12' END OF STMT EXEC #6:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934443118 ===================== PARSING IN CURSOR #6 len=71 dep=2 uid=0 oct=42 lid=0 tim=80934681241 hv=681663222 ad='6b8cb08c' alter session set events '21700 trace name errorstack forever, level 3' END OF STMT PARSE #6:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934681236 BINDS #6: EXEC #6:c=0,e=8426,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934748960 ===================== PARSING IN CURSOR #6 len=34 dep=2 uid=0 oct=42 lid=0 tim=80934753347 hv=1152259314 ad='66a0f058' alter session set sql_trace = TRUE END OF STMT PARSE #6:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934753343 BINDS #6: EXEC #6:c=0,e=29497,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934836162 ===================== PARSING IN CURSOR #7 len=435 dep=1 uid=0 oct=47 lid=0 tim=80934862347 hv=545826169 ad='6b897000' begin execute immediate 'alter session set max_dump_file_size = unlimited'; execute immediate 'alter session set tracefile_identifier = ''10046via_app'''; execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; execute immediate 'alter session set events ''21700 trace name errorstack forever, level 3'''; execute immediate 'alter session set sql_trace = TRUE'; end; END OF STMT
From: John Hurley on 9 Feb 2010 20:20 On Feb 9, 8:01 pm, BD <robert.d...(a)gmail.com> wrote: snip > ... or at least I would be if I had any. > > I am running a copy of 10G XE on a WinXP box. > > In a troubleshooting session some time ago, I turned on some > additional tracing - and quite simply have been unable to turn it off. > > Below is an extract from one of my .trc files in my bdump directory. > It's the alter session statements in this trace that I want to disable > - they're resulting in way more trace files than I need now. > > I've tried ALTER SYSTEM statements, ALTER SESSION statements, to try > to disable these statements, but they keep getting asserted again on > instance restart. > > I'm sure I'll just smack myself in the head once I work this out, but > can someone point me to where I can disable these statements? > > Given that it's XE, it'd probably be just as time-effective to > reinstall as to dial this in... but still. > > Thanks kindly!! > > *** SERVICE NAME:(SYS$USERS) 2010-02-02 09:46:36.593 > *** SESSION ID:(31.46) 2010-02-02 09:46:36.593 > ===================== > PARSING IN CURSOR #6 len=69 dep=2 uid=0 oct=42 lid=0 tim=80934443123 > hv=3164292706 ad='6688fd70' > alter session set events '10046 trace name context forever, level 12' > END OF STMT > EXEC #6:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934443118 > ===================== > PARSING IN CURSOR #6 len=71 dep=2 uid=0 oct=42 lid=0 tim=80934681241 > hv=681663222 ad='6b8cb08c' > alter session set events '21700 trace name errorstack forever, level > 3' > END OF STMT > PARSE #6:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934681236 > BINDS #6: > EXEC #6:c=0,e=8426,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934748960 > ===================== > PARSING IN CURSOR #6 len=34 dep=2 uid=0 oct=42 lid=0 tim=80934753347 > hv=1152259314 ad='66a0f058' > alter session set sql_trace = TRUE > END OF STMT > PARSE #6:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934753343 > BINDS #6: > EXEC #6:c=0,e=29497,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934836162 > ===================== > PARSING IN CURSOR #7 len=435 dep=1 uid=0 oct=47 lid=0 tim=80934862347 > hv=545826169 ad='6b897000' > begin > execute immediate 'alter session set max_dump_file_size = > unlimited'; > execute immediate 'alter session set tracefile_identifier = > ''10046via_app'''; > execute immediate 'alter session set events ''10046 trace name > context forever, level 12'''; > execute immediate 'alter session set events ''21700 trace name > errorstack forever, level 3'''; > execute immediate 'alter session set sql_trace = TRUE'; > end; > END OF STMT Do you have an after login database level trigger that is doing these execute immediates?
From: joel garry on 9 Feb 2010 20:21 On Feb 9, 5:01 pm, BD <robert.d...(a)gmail.com> wrote: > ... or at least I would be if I had any. > > I am running a copy of 10G XE on a WinXP box. > > In a troubleshooting session some time ago, I turned on some > additional tracing - and quite simply have been unable to turn it off. > > Below is an extract from one of my .trc files in my bdump directory. > It's the alter session statements in this trace that I want to disable > - they're resulting in way more trace files than I need now. > > I've tried ALTER SYSTEM statements, ALTER SESSION statements, to try > to disable these statements, but they keep getting asserted again on > instance restart. > > I'm sure I'll just smack myself in the head once I work this out, but > can someone point me to where I can disable these statements? > > Given that it's XE, it'd probably be just as time-effective to > reinstall as to dial this in... but still. > > Thanks kindly!! > > *** SERVICE NAME:(SYS$USERS) 2010-02-02 09:46:36.593 > *** SESSION ID:(31.46) 2010-02-02 09:46:36.593 > ===================== > PARSING IN CURSOR #6 len=69 dep=2 uid=0 oct=42 lid=0 tim=80934443123 > hv=3164292706 ad='6688fd70' > alter session set events '10046 trace name context forever, level 12' > END OF STMT > EXEC #6:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934443118 > ===================== > PARSING IN CURSOR #6 len=71 dep=2 uid=0 oct=42 lid=0 tim=80934681241 > hv=681663222 ad='6b8cb08c' > alter session set events '21700 trace name errorstack forever, level > 3' > END OF STMT > PARSE #6:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934681236 > BINDS #6: > EXEC #6:c=0,e=8426,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934748960 > ===================== > PARSING IN CURSOR #6 len=34 dep=2 uid=0 oct=42 lid=0 tim=80934753347 > hv=1152259314 ad='66a0f058' > alter session set sql_trace = TRUE > END OF STMT > PARSE #6:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934753343 > BINDS #6: > EXEC #6:c=0,e=29497,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934836162 > ===================== > PARSING IN CURSOR #7 len=435 dep=1 uid=0 oct=47 lid=0 tim=80934862347 > hv=545826169 ad='6b897000' > begin > execute immediate 'alter session set max_dump_file_size = > unlimited'; > execute immediate 'alter session set tracefile_identifier = > ''10046via_app'''; > execute immediate 'alter session set events ''10046 trace name > context forever, level 12'''; > execute immediate 'alter session set events ''21700 trace name > errorstack forever, level 3'''; > execute immediate 'alter session set sql_trace = TRUE'; > end; > END OF STMT Well, first check your spfile and see if it's in there. Here's a number of ways to set and disable trace: http://www.petefinnigan.com/ramblings/how_to_set_trace.htm jg -- @home.com is bogus. http://www.willmarryforhealthinsurance.com/
From: Charles Hooper on 10 Feb 2010 06:49 On Feb 9, 8:01 pm, BD <robert.d...(a)gmail.com> wrote: > ... or at least I would be if I had any. > > I am running a copy of 10G XE on a WinXP box. > > In a troubleshooting session some time ago, I turned on some > additional tracing - and quite simply have been unable to turn it off. > > Below is an extract from one of my .trc files in my bdump directory. > It's the alter session statements in this trace that I want to disable > - they're resulting in way more trace files than I need now. > > I've tried ALTER SYSTEM statements, ALTER SESSION statements, to try > to disable these statements, but they keep getting asserted again on > instance restart. > > I'm sure I'll just smack myself in the head once I work this out, but > can someone point me to where I can disable these statements? > > Given that it's XE, it'd probably be just as time-effective to > reinstall as to dial this in... but still. > > Thanks kindly!! > > *** SERVICE NAME:(SYS$USERS) 2010-02-02 09:46:36.593 > *** SESSION ID:(31.46) 2010-02-02 09:46:36.593 > ===================== > PARSING IN CURSOR #6 len=69 dep=2 uid=0 oct=42 lid=0 tim=80934443123 > hv=3164292706 ad='6688fd70' > alter session set events '10046 trace name context forever, level 12' > END OF STMT > EXEC #6:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934443118 > ===================== > PARSING IN CURSOR #6 len=71 dep=2 uid=0 oct=42 lid=0 tim=80934681241 > hv=681663222 ad='6b8cb08c' > alter session set events '21700 trace name errorstack forever, level > 3' > END OF STMT > PARSE #6:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934681236 > BINDS #6: > EXEC #6:c=0,e=8426,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934748960 > ===================== > PARSING IN CURSOR #6 len=34 dep=2 uid=0 oct=42 lid=0 tim=80934753347 > hv=1152259314 ad='66a0f058' > alter session set sql_trace = TRUE > END OF STMT > PARSE #6:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934753343 > BINDS #6: > EXEC #6:c=0,e=29497,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=80934836162 > (SNIP) I agree with John - take a look at the number after dep= in the trace file - that is indicating that it is very likely a trigger that is executing these calls, but it could also be an anonymous PL/SQL block. You might try the following SQL statement to see if there is a logon or startup trigger: SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT FROM DBA_TRIGGERS WHERE OWNER='SYS' AND TRIGGER_TYPE IN ('BEFORE EVENT','AFTER EVENT') AND SUBSTR(TRIGGERING_EVENT,1,5) IN ('START','LOGON'); TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT ---------------------- ------------ ---------------- LOGON_FIX_MYAPP_PERF AFTER EVENT LOGON LOGON_CAPTURE_10046 AFTER EVENT LOGON ENABLE_OUTLINES_TRIG AFTER EVENT STARTUP If the above returns no rows, there is a small chance that the trigger was created by another user - remove the OWNER criteria from the WHERE clause. If you *know* that the trigger does not belong there, connect as SYS and drop the trigger: DROP TRIGGER LOGON_CAPTURE_10046; Charles Hooper Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table" http://hoopercharles.wordpress.com/ IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: John Hurley on 10 Feb 2010 09:02
On Feb 10, 6:49 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: snip > I agree with John - take a look at the number after dep= in the trace > file - that is indicating that it is very likely a trigger that is > executing these calls, but it could also be an anonymous PL/SQL > block. You might try the following SQL statement to see if there is a > logon or startup trigger: It is a little hard to believe that someone would have a trigger in effect that they forgot about but ... > Charles Hooper > Co-author of "Expert Oracle Practices: Oracle Database Administration > from the Oak Table"http://hoopercharles.wordpress.com/ > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc. Hey congrats on the whole Oak Table thing. It looks like you have an event coming up in Ann Arbor ( one of my favorite towns ... I spend a couple of years there while in junior high school ) that looks epic. You must be michigan based? I was unaware of that ... Is there some url that you can give me ( email back perhaps ) with more details? Do you feel like getting dragged down to Cleveland to present one of these days at our local Oracle Users Group? ( http://www.neooug.org ) ... |