|
SQL
Trace is writing 100s of trace or dump files into user dump directory. How can I stop it?
|
More Resources by
Google: |
|
|
|
|
By:
John Kazerooni
When
you open a trace file, it does show that the sql tracing at the session level is being enabled with
the command "alter session set sql_trace true"
The
trace includes the session id and serial number
SESSION
ID:(39.922)
sid
39 serial# 922
Look
at the trace files to see if they all have the same sid and serial#
If
so then this might be set at the session level by single user.
If
the sid and serial# are different, then may be set in the code itself.
You
can turn off trace for a sid,serial# using the following package
DBMS_SUPPORT.STOP_TRACE_IN_SESSION(
sid , null );
If
you have too many sessions to stop this could be a lot of work and if sqltrace is in the code, then
tracing may start again.
Using
this sid, serial# information you can look at the views v$session and v$process to get info on the
oracle user and maybe even more info on osuser, application and terminal.
As
a temp fix to prevent the filling of diskspace you can set the parameter MAX_DUMP_FILE_SIZE =0 or a
small value to prevent large files.
ALTER
SYSTEM SET max_dump_file_size=0;
Also,
the sql trace requires the privilege Alter session.
You
could revoke that privilege to prevent the tracing. This will also prevent other alter session
commands.
REVOKE
alter session FROM dmadmin;
Otherwise,
no easy method to turn off the session sql tracing.
Good Luck!
|