SQL tracing for Oracle Database

SQL tracing for Oracle Database

Current Session Trace

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

You can find the trace file through below queries:

To find the trace file for your current session:

SELECT value FROM v$diag_info WHERE name = ‘Default Trace File’;

To find all trace files for the current instance:

SELECT value FROM v$diag_info WHERE name = ‘Diag Trace’;

To determine the trace file for each Oracle Database process:

SELECT pid, program, tracefile FROM v$process;

To find the trace file with specific name:

SQL> select value from v$diag_info where value like '%tracefilename%';

Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Tracing other user's sessions

DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions.

Steps:

Get the SID and SERIAL# for the process you want to trace.

SQL> select sid, serial# from sys.v_$session where username = ‘MOHY’;

SID SERIAL#
---------- ----------
18 54

Enable tracing for your selected process:

SQL> ALTER SYSTEM SET timed_statistics = true;

SQL> execute dbms_system.set_sql_trace_in_session(18, 54, true);

Ask user to run just the necessary to demonstrate his problem.

To find if trace file has been created:

SQL> select value from v$diag_info where value like '%MOHY%';

Disable tracing for your selected process:

SQL> execute dbms_system.set_sql_trace_in_session(18,54, false);

To enable SQL tracing for the entire database:

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;

To stop:

ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;