v$lock：视图列出当前系统持有的或正在申请的所有锁的情况
v$locked_object：视图列出当前系统中哪些对象正被锁定
1）、查询当前数据库锁的情况，以及导致锁的sql语句：
|
1
2
3
|
Select
object_name as
对象名称,s.sid,s.serial#,p.spid as
系统进程号,sq.SQL_TEXT
From
v$locked_object l , dba_objects o , v$session s , v$process p,v$sqlarea sq
Where
l.object_id=o.object_id and
l.session_id=s.sid and
s.paddr=p.addr and
s.SQL_ADDRESS = sq.ADDRESS;
|
2）、杀掉非正常的一些锁
|
1
|
alter
system kill session ‘sid,serial#‘;
|
3）、批量杀掉未释放的锁
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
declare
cursor
mycur is select
b.sid,b.serial#
from
v$locked_object a,v$session b
where
a.session_id = b.sid group
by
b.sid,b.serial#;
begin for
cur in
mycur
loop
execute
immediate ( ‘alter system kill session ‘‘‘||cur.sid || ‘,‘|| cur.SERIAL# ||‘‘‘ ‘);
end
loop;
end;
|