ORACLE查询锁表并解锁

--查询锁表

select l.OS_USER_NAME,
       o.owner,
       o.object_name,
       o.object_type,
       s.SID,
       s.SERIAL#,
       p.SPID
  from v$locked_object l, dba_objects o, v$session s, v$process p
 where l.OBJECT_ID = o.object_id
   and l.SESSION_ID = s.sid
   and s.paddr = p.ADDR
 order by o.object_id;


--解锁

alter system kill session ('SID','SERIAL#');

 

--批量解锁

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;