Oracle锁表与解锁的方案

database

--ORACLE表被锁原因：具体操作某一个FORM界面，或者后台数据库操作某一个表时发现一直出于"假死"状态，

--可能是该表被某一用户锁定，导致其他用户无法继续操作

--查询被锁的表

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;

--查看是哪个session引起的

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;

--杀掉对应进程即解锁

alter system kill session "866,20840" --其中866是sid 20840是serial#

--解锁表 ==>查看对应的锁定表找到s.sid s.serial# 两个字段的值

--权限 system/oracle

SELECT /*+ rule */

s.username,

decode(l.type, "TM", "TABLE LOCK", "TX", "ROW LOCK", NULL) LOCK_LEVEL,

o.owner,

o.object_name,

o.object_type,

s.sid,

s.serial#,

s.terminal,

s.machine,

s.program,

s.osuser

FROM v$session s, v$lock l, dba_objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id(+)

AND s.username is NOT Null

and s.OSUSER IN ("用户名")

--然后杀死进程解锁 alter system kill session "s.sid,s.serial#"

alter system kill session "429,55473";