Generating SQL trace

TURN on SQL tracing:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

TURN off SQL tracing:

ALTER SESSION SET EVENTS '10046 trace name context off';


Location of trace dump file:

Trace output is written to the database's UDUMP directory.

UDUMP is the database's USER DUMP DIRECTORY, you can find the same by using:
SQL> SHOW PARAMETERS user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /xx/xx/xx/xx/udump

To change this value:
SQL> ALTER SYSTEM SET user_dump_dest = '/xx/xx/xx/xx/udump' SCOPE=both;

System altered.


The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:
INSTANCE is the name of the Oracle instance,
PID is the operating system process ID (select SPID from V$PROCESS); and
TRACEID is a character string of your choosing.

 select vp.spid
 from v$session vs,
      v$process vp
 where sid IN (select distinct sid from v$mystat )
   and vs.paddr = vp.addr;