Re: Unable to disable tracing - XE
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 10 Feb 2010 03:49:05 -0800 (PST)
Message-ID: <055ab001-5e2d-417c-ad28-43ae1a46a28d_at_l19g2000yqb.googlegroups.com>
On Feb 9, 8:01 pm, BD <robert.d..._at_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
Date: Wed, 10 Feb 2010 03:49:05 -0800 (PST)
Message-ID: <055ab001-5e2d-417c-ad28-43ae1a46a28d_at_l19g2000yqb.googlegroups.com>
On Feb 9, 8:01 pm, BD <robert.d..._at_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.
Received on Wed Feb 10 2010 - 05:49:05 CST