Unable to disable tracing - XE
BD
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
John Hurley
snip
Do you have an after login database level trigger that is doing these
execute immediates?
joel garry
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/
Charles Hooper
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.
John Hurley
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
) ...
Charles Hooper
> 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
> ) ...
Thanks - I was offered membership to the OakTable in 2008, and had the
opportunity to participate in the latest book that was written by
OakTable members.
The first announcement of the Michigan OakTable Symposium appeared
here:
http://hoopercharles.wordpress.com/2010/01/08/the-oaktable-network-invades-michigan-usa-advert/
A second announcement should appear in a couple of days with more
detailed information (watch my blog or orana.info). I received word
that Tanel Poder, Tim Gorman, Jeremiah Wilton, Alex Gorbachev and a
couple other people from around the world, in addition to those listed
at the above link, have offered to present. It also looks like I will
have the opportunity to co-present with Randolf Geist, who will be
flying in from Germany.
The registration site is in the process of being tested, and we are
still finalizing who will be presenting, what will be presented, and
how information about the event will be organized. The official
website for the event is: http://michigan.oaktable.net
I appreciate the offer, but time constraints and the current economic
conditions are limiting factors at the moment.
joel garry
> On Feb 10, 6:49 am, Charles Hooper <hooperc2...@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 ...
>
Not hard to believe at all, it's easy to forget to unwind every bit of
instrumentation turned on in the heat of a problem, especially with
multiple cooks involved. I didn't even think of a logon trigger
because I so rarely do it that way, forehead-slapping obvious now that
you guys pointed it out. There was a time I ran startup scripts to
pin stuff in the shared library too, but I haven't done that in a very
long time, either...
I'm still finding stuff in odd places from a remote developer while I
was on vacation last year. Take a complicated stack and put pressure
on to get things done, all sorts of weird happens. Then you have to
figure out what was fanned and what is necessary but named wrong.
Heck, even Oracle has delivered with trace turned on (otr, was it
7.3.2?). Oops!
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2010/feb/10/barber-finds-fame/
BD
> It is a little hard to believe that someone would have a trigger in
> effect that they forgot about but ...
Oh, believe it.
A bit embarassing, but there you go.
Basically I had been troubleshooting a problem involving using
Replication to replicate a table with a custom datatype. Even if I was
not selecting the columns with the custom datatype in the SELECT
statement of my MV, it errored out - but *only* over a db link.
In hindsight, the cause of the issue was likely not about the db link,
but rather that the schema owner of the source table didn't own the
type definition; it was owned by another schema in the source db.
The ID which was receiving the replicated data didn't have grants on
the type definition, only on the data in the table.
Anyway. I quite honestly have no recollection of creating that trigger
- partly because of the hair-on-fire way I needed to resolve the issue
at the time, and partly because it took a long time for the symptoms
of leaving the trigger in place (trace file bloating) to become
evident. But there it is.
Or rather, there it was. ;)
Thanks, John. I was about to throw in the towel, and just delete the
install of XE and start over.
*Clearly*, I need to document things more effectively. Oi... ;)
John Hurley
snip
> > It is a little hard to believe that someone would have a trigger in
> > effect that they forgot about but ...
>
> Oh, believe it.
>
> A bit embarassing, but there you go.
Well glad I could help ... even a blind mouse finds a piece of cheese
every once in a while right?