【Oracle】Oracel锁

一、查看锁

--1、查看锁

select * from v$locked_object;

--2、查看锁sid

select * from v$session where sid = 589;

--3、查看被锁的表

SELECT P.SPID,
A.SERIAL#,
C.OBJECT_NAME,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME
FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, ALL_OBJECTS C
WHERE P.ADDR = A.PADDR
AND A.PROCESS = B.PROCESS
AND C.OBJECT_ID = B.OBJECT_ID;

--4、查看被锁SQL

SELECT A.USERNAME,
A.MACHINE,
A.PROGRAM,
A.SID,
A.SERIAL#,
A.STATUS,
C.PIECE,
C.SQL_TEXT
FROM V$SESSION A, V$SQLTEXT C
WHERE A.SID IN (SELECT DISTINCT T2.SID
FROM V$LOCKED_OBJECT T1, V$SESSION T2
WHERE T1.SESSION_ID = T2.SID)
AND A.SQL_ADDRESS = C.ADDRESS(+)
AND SID = 589
ORDER BY C.PIECE;

二、解锁

Ora-00054:resource busy and acquire with nowait specified

--1、查询哪些对象被锁：
select object_name, machine, s.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.object_id　 = 　o.object_id
and l.session_id = s.sid;

--2、杀死一个进程：
alter system kill session 'sid, serial#'; -- immediate

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

ORA-00031: session marked for kill

-- 1、查询spid

select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = 589

-- 2、在unix上，用root身份执行命令: 

#kill -9 521（即上一步查询出的spid）

 

http://www.cnblogs.com/Ronger/archive/2011/12/19/2293509.html

 

http://www.cnblogs.com/chuanzifan/archive/2012/05/26/2519695.html