Kill the Oracle deadlock Process

First, check which tables are locked:

Select B. owner, B. object_name,. session_id,. locked_mode from V $ locked_object A, dba_objects B where B. object_id =. object_id; Owner object_name session_id locked_mode invalid WSSB limit 22 3 wssb_rtrepos limit 24 2 wssb_rtrepos limit 29 2 wssb_rtrepos limit 39 2 WSSB limit 47 3 wssb_rtrepos limit 47 3 select B. username, B. sid, B. serial #, logon_time from V $ locked_object A, V $ session B where. session_id = B. sid order by B. logon_time; username Sid serial # logon_time -------------------------------- -------- ------- wssb_rtaccess 39 1178 2006-5-22 1 wssb_rtaccess 29 5497 2006-5-22 1

Kill sessions in a process:

Alter system kill session 'sid, serial # '; e. g alter system kill session '2017 7 ';

If there is a ora-00031 error, add immediate; Alter system kill session '2017 7' immediate;

How to kill the Oracle deadlock Process

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 clocks! = '0 ';

2. Check which Sid is used to find the session:

Check the V $ access View:

Select * from V $ access where owner = 'user of the process' and name = 'process name just found ';

3. Locate 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 the process:

(1) Kill the Oracle process first:

Alter system kill session 'sid, serial #';

(2) then kill the operating system process:

Kill-9 The spid you just found or the spid you just found by orakill.

Oracle deadlock

Query database deadlocks:

Select t2.username | ''| t2.sid |'' | t2.serial # | ''| t2.logon _ time |'' | t3. SQL _ text from V $ locked_object T1, V $ session T2, V $ sqltext T3 where t1.session _ id = t2.sid and t2. SQL _ address = t3.address order by t2.logon _ time;

The result of the query is a deadlocked session. The following is killing the session. Obtain the queried Sid and serial # and fill in the following statement:

Alter system kill session 'sid, serial #';

Generally, the deadlock in the database can be solved, or the corresponding operating system process can be found through the session ID to kill the operating system process in UNIX.

Select. username, C. spid as OS _process_id, C. PID as oracle_process_id from V $ session A, V $ Process C Where C. ADDR =. paddr and. SID = and. serial # =;

Then kill (UNIX) or orakill (Windows) is used ).

In Unix:

PS-Ef | grep OS _process_id kill-9 OS _process_id PS-Ef | grep OS _process_id

This problem is often encountered during the use of Oracle, so a few solutions are also summarized.

1) Find the deadlock process:

Sqlplus "/As sysdba" (sys/change_on_install) 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;

Replace the SID with the SID of the deadlock:

Exit PS-Ef | grep spid

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