Oracle 查询锁表，锁行，批量解锁语句记录

记录Oracle锁表查询与解锁语句，所有语句均网上查询所得。记录方便日后查找。

1 查询锁表语句

select sql_text from v$sql a,v$session b
where a.sql_id=b.sql_id and b.event='enq: TX - row lock contention';

2 查询锁表记录

SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id  AND l.session_id = s.sid;

3 单表解锁

参数依赖上面语句查询的结果

ALTER SYSTEM KILL SESSION '2486,42567'; --【sid,serial#】

4 批量解锁所有表

利用游标

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;