in Oracle

Trace Files — 12 : Tracing a Particular Process

Unlike tracing for particular SQL statements, you can also trace by PID (Oracle PID) or Server Process ID (SPID).

SQL> select s.sid, p.pid
2 from v$session s join v$process p
3 on (s.paddr=p.addr)
4 and s.username = 'HEMANT';

SID PID
---------- ----------
19 22

SQL>
SQL> alter system set events 'sql_trace {process: orapid=22}';

System altered.

SQL>
SQL> select s.sid, p.pid, p.spid
2 from v$session s join v$process p
3 on (s.paddr=p.addr)
4 and s.username = 'HR';

SID PID SPID
---------- ---------- ------------------------
14 26 3207

SQL> alter system set events 'sql_trace {process:3207}';

System altered.

SQL>

Tracing for the processes is disabled with :

SQL> alter system set events 'sql_trace {process: orapid=22} off';

System altered.

SQL> alter system set events 'sql_trace {process:3207} off';

System altered.

SQL>

Tracing seems to get disabled after the next SQL, not immediately.

Remember : This is SQL Tracing, not Optimizer Tracing.
.
.
.