Tracing the process SPID in Oracle

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.