一、查看锁

--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/chuanzifan/archive/2012/05/26/2519695.html