SQL Trace is writing 100s of trace or dump files into user dump directory. How can I stop it?

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!