Tracing the process SPID in Oracle

1. Identified the process id if it present in alert log from any error or trace it from your self from SQL query with v$process view.

--- From alert log
WARNING: too many parse errors, count=1178 SQL hash=0xb94e2a38
PARSE ERROR: ospid=2776, error=918 for statement:
2018-12-03T16:59:31.252004-06:00

--From SQL Query trace yourself
select p.PID,p.SPID,s.SID from v$process p,v$session s
where s.paddr = p.addr and s.sid = &SESSION_ID;


2. Login sqlplus as sysdba and start the trace process

sqlplus / as sysdba
oradebug setospid XXXX; -- XXXX is process id
oradebug unlimit;
oradebug tracefile_name;
oradebug event 10046 trace name context forever,level 12;

Example:
sqlplus / as sysdba
oradebug setospid 2776;
oradebug unlimit;
oradebug tracefile_name;
oradebug event 10046 trace name context forever,level 12;

3. Wait for the issue to be reproduced.

Wait for error to occurred again.

4. Disable the trace process.

oradebug event 10046 trace name context off;
exit