oracle 锁表解锁

1. 查锁表
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
2. 查进程
SELECT sid, serial#, username, status, osuser FROM v$session  where serial# = '11016'
3. 杀锁
alter system kill session '82,12111';

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;

5.批量杀锁,先查出来.拼接成可执行sql语句

SELECT OBJECT_NAME,
       MACHINE,
       S.SID,
       S.SERIAL#,
       'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''';' execution
  FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S
 WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID;