|
"It
is a wholesome and necessary thing for us to turn again to the
earth and in the contemplation of her beauties to know of
wonder and humility."
-Rachel
Carson (1907-1964)
|
SQL Trace is writing
100s of trace or dump files into user dump directory. How can I stop
it?
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.
|