The Oracle (tm) Users' Co-Operative FAQ

I have deleted a trace file with the session still live. I now want the session to resume tracing, but a new file doesn't appear.

Author's name: Jonathan Lewis

Author's Email:

Date written: 20th June 2001

Updated: 24th Jan 2005

Oracle version(s): 8.1 - 9.0

From Unix, if you switch sql_trace off, delete the trace file, and switch sql_trace on again for a given session, Oracle does not appear to create a new trace file. This can be a nuisance, and there is no documented workaround. NT is a more friendly, and a new trace file will be generated every time you delete the old one.

Back to index of questions

The reason why you have a problem under Unix is that the trace file is not closed - even if you set sql_trace to false. There is, however, a method for sorting the problem out using oradebug. This is not, alas, very elegant - but may be adequate in many cases where a DBA is exerting external control of the tracing.

Start up SQL*Plus as sysdba, locate the process id (oracle or unix) of the target user, e.g.

select spid, pid
from v$process
where addr = (
        select paddr 
        from v$session
        where sid = 99
oradebug setorapid {the pid above}
oradebug setospid {the spid above}
oradebug flush         -- flushes any remaining data to the trace file
oradebug close_trace   -- closes the trace file.

The nice thing about the close_trace option is that you can do it even after you have deleted the trace file from the operating system level. In fact in most cases you may have to delete the trace file before you issue a close_trace, otherwise you may find that Oracle re-opens to write the next bit of trace information to it, and the problem simply repeats itself.

Update 24th Jan 2005

There is a simple solution to this problem if you are allowed to use it in your environment.

    Alter session set tracefile_identifier = ‘text string’;

If you can issue this command, then the current trace file is closed and a new one is opened with the ‘text string’ included as part of the file name.

This is also very convenient for running a series of tests and dropping the test results into separate trace files. Bear in mind that some of the odds and ends (such as STAT lines) may end up in the wrong trace file, though.

Further reading: N/A

Back to top

Back to index of questions