Too many trace files generation

[st2000]

Hello Guys:

Every minute I am generating about 10 trace files in my udump directory since yesterday afternoon.

I TKPROFed a few file but does not make much sense, to know how to stop trace files getting generated..

An average trace file is like this.. when i checked randomly for the last few hours

TKPROF: Release 8.1.7.1.0 - Production on Fri Aug 16 10:29:28 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Trace file: ././ora_48758_odbmsprd.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: ././ora_48758_odbmsprd.trc
Trace file compatibility: 8.00.04
Sort options: default

1 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
22 lines in trace file.


Thanks, Sashi

[fraze]

if you've set tracing on at the database level they're probably from your background processes

[st2000]

Hello Pando:

I did implement or try to execute a trace using logon trigger The trc files are indicating some one has and does not have the permissions to do
so. See the following notes:

From the alert log:

Errors in file /home/oracle/admin/ODBMSPRD/udump/ora_60654_odbmsprd.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 4
Fri Aug 16 10:20:36 2002


From udump:

PROD box [AIM]: more ora_61266_odbmsprd.trc
Dump file /home/oracle/admin/ODBMSPRD/udump/ora_61266_odbmsprd.trc
Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
With the Partitioning option
JServer Release 8.1.7.1.0 - Production
ORACLE_HOME = /home/oracle/product/8.1.7
System name: AIX
Node name: CG_JDA1
Release: 3
Version: 4
Machine: 0007904F4C00
Instance name: ODBMSPRD
Redo thread mounted by this instance: 1
Oracle process number: 71
Unix process pid: 61266, image: oracle@CG_JDA1 (TNS V1-V3)

*** SESSION ID:(330.524) 2002-08-16 10:20:45.951
Skipped error 604 during the execution of COTPROD.TRACE
*** 2002-08-16 10:20:45.964
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 4

Remember from my previous note, I created a logon trigger as "non-SYS user" by the time you came back to say that should be created as "SYS" user..

This is generating tonnes of trc files in udump and in bdump alert.log files entries..

We have bounced the db for cold backup yday and I have also dropped the trigger this morning as well...

Please suggest..

Thanks, ST2000

[st2000]

OK. The tracing stopped..

Just had to log in and drop the trigger...

Only thing left is to clean up the mess in udump and bdump.

Can anyone tell, how to delete all tracefiles after yesterday 4:00 pm or say.. delete all trace files that has

0 statements explained in this session..

All trace files that are dumb have this statement at the end of them and I guess there are many many files like this..

Thanks, Sashi

[pando]

I guess you have not dropped the trigger created by non-sys user

[st2000]

I did drop the trigger Pando and I have also mentioned that in my mail...After that the generation of trc files stopped.. There are about 9000 files (trc files) got created since yday 4:30 pm and till today 10:30 am (time I dropped the trigger).

Now I am just cleaning up the trc files..

How can I drop the trc files created between this time only..

Secondly, I requested for someone to help with the command with "0 statements explained".. please ignore that since those are from TKPROFed trace files and not from trace files..

Thanks, ST2000

[st2000]

I just dropped all the trace files in the last 24 hours..

Thanks, ST2000

[pando]

what i meant is the trace files are generated is because you have not dropped the trigger not owned by SYS

because sys wont give insufficient privilege error since it has explicit alter session and not through a role