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;