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
