How to trace session(s)

Using DBMS_SUPPORT package

Starting Trace:
Alter system set max_dump_file_size=unlimited;
exec sys.dbms_support.START_TRACE_IN_SESSION(sid,serial#,waits=>TRUE, binds=>TRUE ) ;

Stopping Trace:
Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION( sid , null )
Alter system set max_dump_file_size=102400;

Generating Start-Stop Trace script for sessions (trace.sql):

ACCEPT USERNAME char prompt 'Enter the User Name > '
variable b0 varchar2(50)
exec :b0 := upper('&USERNAME');

select '------------ START TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Alter system set max_dump_file_size=unlimited;' TRACE_SCRIPT from dual
union all
select 'exec sys.dbms_support.START_TRACE_IN_SESSION('||sid||','||serial#||',waits=>TRUE,binds=>TRUE ) ;' TRACE_SCRIPT from v$session where username=:b0
union all
select '------------ STOP TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION('||sid||',null);' TRACE_SCRIPT from v$session where username=:b0
union all
select 'Alter system set max_dump_file_size=102400;' TRACE_SCRIPT from dual
/

Using Oradebug

Find server process id of the session:

select spid from v$session s ,v$process p where s.paddr=p.addr and s.sid=&sid

Starting Trace:
sqlplus '/ AS SYSDBA'
SQL> oradebug setospid {spid}
Oracle pid: {pid}, Unix process pid: {spid}, image: oracle@... (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

Stopping Trace:
SQL> oradebug event 10046 trace name context off
Statement processed.