1. Find the session to be monitored
SELECT username, sid, serial#, program FROM v$session WHERE username = <User_Name>;
2. Enable SQL Tracing
a, Current Session:
ALTER SESSION SET sql_trace = TRUE; or execute dbms_session.set_sql_trace(true);
b, Different Session (as SYSDBA):
execute dbms_system.set_sql_trace_in_session(sid, serial#, sql_trace); e.g. execute dbms_system.set_sql_trace_in_session(114, 4667, TRUE);
3. Enable Oracle database to gather statistics
ALTER SYSTEM SET timed_statistics = true; -- at system level ALTER SESSION SET timed_statistics = true; -- at session level
4. Formatting the output with TKPROF
TKPROF inputfile outputfile [OPTIONS] e.g. tkprof mydb_ora_11915.trc /tmp/tkprof1.txt SYS=NO
5. Find directory where trace file is generated
SELECT value FROM v$parameter WHERE name='user_dump_dest';
6. Identify trace file generated
SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid FROM v$session s, v$process p WHERE p.addr = s.paddr and s.username = <User_Name>;
7. Disable tracing for the session
ALTER SESSION SET sql_trace = TRUE; OR EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);