TRACE ON/OFF ???

[dbafreak]

Can someone here please explain what is TRACE ON/OFF used for? Please explain the basics and how it is used. Expecting some feedback from the gurus.

[marist89]

Depends, what are you talking about?

If you are trying to trace somebody else's session, you can:
exec sys.dbms_system.set_sql_trace_in_session(sid, serial#, true);

This will start a SQL trace of the session and produce a trace file in the server's udump directory. You can then use tkprof to format this output to see which of your queries is misbehavin...

To turn tracing off, use:
exec sys.dbms_system.set_sql_trace_in_session(sid, serial#, false);

There are other levels of detail you can get with dbms_system.set_ev, but unless you know and understand the consequences, I wouldn't recommend using them.

You can set tracing on for your whole instance by setting an init.ora parameter, sql_trace (I think).

You can also set tracing on for YOUR session by issuing:
ALTER SESSION SET SQL_TRACE=TRUE;


[Edited by marist89 on 03-19-2002 at 09:48 AM]

[marist89]

Originally posted by Mr.Hanky
Jeff; Is that a typo? You have the syntax identical for turning tracing on and off?

????

[Mr.Hanky]

I was framed!! Shame on you!!

MH

[marist89]