Oracle kills Lock Table process (forwarding + merge + own practice)

One:

In Oracle database operations, we sometimes use lock table queries and operations such as unlocking and kill processes

(1) The Code of the Lock table query has the following form:
Select COUNT (*) from V$locked_object;
SELECT * from V$locked_object;
(2) See which table is 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;
(3) See which session is causing the

(4) Kill the corresponding process
Execute command: Alter system kill session ' 1025,41 ';
1025 of them are sid,41 for serial#.

The second:

First look at 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_notify_queue_tab 29 2
Wssb_rtrepos Wb_rt_notify_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 2006-5-22 1

Kill conversation

Alter system kill session ' sid,serial# ';

Alter system kill session ' 29,5497 ';

If there is a ora-00031 error, then add immediate at the back;

Alter system kill session ' 29,5497 ' immediate;

-------------
How to kill the Oracle deadlock process
Oracle's deadlock is a headache, summarizing some of the experience as a learning note

1. Check which process is locked
Check V$db_object_cache view:
SELECT * from V$db_object_cache WHERE owner= ' process belongs to user ' and clocks!= ' 0 ';

2. Check which SID, through SID, can know which session.
Check v$access view:
SELECT * from v$access WHERE owner= ' process the owning user ' and Name= ' the process name just found ';

3. Identify SIDs and serial#
Check v$session view:
SELECT sid,serial#,paddr from v$session WHERE sid= ' SID ' just found '
Check v$process view:
SELECT SPID from v$process WHERE addr= ' just found paddr ';

4. Killing process
(1). Kill the Oracle Process first:
ALTER SYSTEM KILL SESSION ' isolated sid, isolated serial# ';
(2). Kill the operating system process again:
KILL-9 just found the SPID
Or
Orakill just detected the SID just detected by the SPID

------------------
Oracle's Deadlock
Querying the database for deadlock
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 deadlock session,
Here's the Kill.
Get the SID and serial# from the above query, and fill in the following statement

Alter system kill session ' sid,serial# ';

In general, you can resolve the deadlock that exists in the database.

or through the session ID to find the corresponding operating system process, in UNIX to kill the operating system process.
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 use Kill (Unix) or Orakill (Windows)

In Unix
Ps-ef|grep os_process_id
Kill-9 os_process_id
Ps-ef|grep os_process_id

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

Often encountered this problem in the use of Oracle, so also summed up a little solution:)
1) To 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# '; (where sid=l.session_id)
3) If it doesn't work,
Select Pro.spid from V$session ses,v$process Pro where ses.sid=xx and ses.paddr=pro.addr;
Where the SID is replaced with a dead-lock SID.
Exit
Ps-ef|grep spid
Where SPID is the process number of this process, kill the Oracle process.

Oracle kills Lock Table process (forwarding + merge + own practice)