If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.
The first step is to identify the session to be traced by some means:
For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
SPID is the operating system Process identifier (os pid)
Once the OS process id for the process has been determined then the trace can be initialised as follows:
Lets assume that the process to be traced has an os pid of 9834.
Login to SQL*Plus as a dba and execute the following:
connect / as sysdba
oradebug setospid 9834
oradebug event 10046 trace name context forever,level 12
Remember to replace the example ‘9834’ value with the actual os pid.
To disable oradebug tracing once tracing is finished:
oradebug event 10046 trace name context off