How to enable trace in Oracle

How to enable trace in Oracle

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;


2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or - 

EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;


3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);

- or -

-- Oracle 10.1
EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>TRUE);
EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>FALSE);

[@more@]