Oracle query locks and kill locks

Code
(Reprinted)
Add to favorites the original oracle deadlock killing Process

First, check which tables are locked.

Select B. owner, B. object_name, a. session_id, a. locked_mode
From v $ locked_object a, dba_objects B
Where B. object_id = a. object_id;


OWNER OBJECT_NAME SESSION_ID LOCKED_MODE
---------------------------------------------------------------------------
WSSB SBDA_PSHPFTDT 22 3
WSSB_RTREPOS WB_RT_SERVICE_QUEUE_TAB 24 2
WSSB_RTREPOS wb_rt_policy_queue_tab 29 2
WSSB_RTREPOS wb_rt_policy_queue_tab 39 2
WSSB SBDA_PSDBDT 47 3
WSSB_RTREPOS WB_RT_AUDIT_DETAIL 47 3

Select B. username, B. sid, B. serial #, logon_time
From v $ locked_object a, v $ session B
Where a. 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 1


Kill session

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 '2010 7' immediate;


-------------

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. 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 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


------------------
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 query result is a deadlock session,
The following is how to kill
Obtain the obtained SID and SERIAL #, and fill in the following statement.

Alter system kill session 'sid, serial #';

Generally, the database deadlock can be solved,

Or find the corresponding operating system process through the session id, and kill the operating system process in unix.
SELECT a. username, c. spid AS OS _process_id, c. pid AS oracle_process_id FROM v $ session a, v $ process c
WHERE c. addr = a. paddr and a. sid = and a. 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 I also summarized some solutions :)
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;
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 process.