tracing SQL in Oracle

If you want to know what SQL statements are actually being executed by an application because something goes wrong and there is no clear error message explaining what’s happening you can use SQL Trace. SQL Trace can be enabled on the current session, the session of another user or the complete database.

To enable SQL Trace on the current session

alter session set sql_trace = true;

You can find the output of the trace in the udump directory of the database (see the value of parameter user_dump_dest). You can chose to give the tracefile another name, see the next example:

alter session set sql_trace = true; alter session set tracefile_identifier = customname;

When done tracing you’ll need to stop the trace:

alter session set sql_trace = false;

To enable SQL trace on another session
You’ll first need to know which session to trace, by looking up the sid and serial#:

select s.sid, s.serial#, s.osuser, s.program from v$session s;

Start the trace on sid number 8 and serial 13607:

alter system set timed_statistics = true; execute dbms_system.set_sql_trace_in_session(8, 13607, true);

Carry out the to trace actions and stop the trace:

execute dbms_system.set_sql_trace_in_session(8,13607, false);

You can find the output again in the udump destination.

Trace the complete database

Start SQL Trace on the complete database:

alter system set sql_trace = true scope=memory;

Stop SQL Trace on the complete database:

alter system set sql_trace = false scope=memory;