How to kill deadlocks in oracle

Run the following statement to view the deadlock:

Select/* + rule */lpad ('', decode (l. xidusn, 0, 3, 0) | l. oracle_username User_name, o. owner, o. object_name, o. object_type, s. sid, s. serial #, p. spidfrom v $ locked_object l, dba_objects o, v $ session s, v $ process p where l. object_id = o. object_idand l. session_id = s. sidand s. paddr = p. addr order by o. object_id, xidusn DESC;

 

(The purpose of executing this statement is to find the SID and SERIAL # Of the deadlock you need to kill. Note: we do not recommend that you kill others' deadlocks, it is possible that someone else is locking the table while you are querying the deadlock, and you are querying the deadlock. In fact, when you query the deadlock again, someone else's lock has been resolved. Therefore, it is not recommended to kill the lock of your own table .) Now, find the table name you want to operate on, find the corresponding SID and SERIAL # to replace the variables in the following statement, and then execute the command to unlock the table.

Alter system kill session 'sid, serial #';

 

If the SID and SERIAL # are respectively: 35, then you should execute the statement to kill the deadlock as follows:

Alter system kill session '2017 0 ';

 

Then execute the first statement to see if your deadlock has been killed? It will certainly succeed!