How to find deadlocks in sessions in Oracle databases

The following article mainly describes how to find the deadlock solution in the session in the Oracle database. I saw the relevant content on a highly reliable online warfare two days ago. I will share it with you today, if you are interested in the actual operations, you can click to view the following articles.

1. Check which process is locked

Check the V $ DB_OBJECT_CACHE View:

SELECT * from v $ DB_OBJECT_CACHE where owner = 'user of the process' and locks! = '0 ';

2. query which SID is used to identify which SESSION.

Check the V $ ACCESS View:

SELECT * from v $ access where owner = 'user of the process' and name = 'process NAME just found ';

3. Identify SID and SERIAL #

Check the V $ SESSION View:

Select sid, SERIAL #, paddr from v $ session where sid = 'sid just found'

View V $ PROCESS:

Select spid from v $ process where addr = 'paddr just found ';

4. Kill processes

(1) first kill the Oracle database process:

Alter system kill session 'sid, SERIAL #';

(2). Then kill the operating system process:

KILL-9 The SPID just found

Or

ORAKILL the SPID that SID just found

Method 2:

This problem is often encountered during the use of Oracle, so I also summarized some solutions :)

1) Find the deadlock process:

 
 

  
  
sqlplus "/as sysdba"  
  
  
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.Oracle_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT 
l,V$SESSION S WHERE l.SESSION_ID=S.SID;  
 
 

2) kill the deadlock process:

 
 

  
  
Alter system kill session 'sid, serial # '; sid = l. session_id)
 
 

3) if the problem persists,

 
 

  
  
select pro.spid from v$session ses,
v$process pro where ses.sid=XX and ses.paddr=pro.addr; 
 
 

The sid is replaced by the sid of the deadlock.

 
 

  
  
exit  
  
  
ps -ef|grep spid  
 
 

Spid is the process Number of the process and kill the Oracle database process.