oracle查询锁表

oracle查询锁表情况

select t1.ORACLE_USERNAME,
   t1.OS_USER_NAME,
   t2.OWNER,
   t2.OBJECT_TYPE,
   t2.OBJECT_NAME,
   t2.status,
   t3.SID,
   t3.SERIAL#,
   t4.PID,
   t4.SPID
  from v$locked_object t1, dba_objects t2, v$session t3,  v$process t4
 where t1.OBJECT_ID = t2.object_id
   and t1.SESSION_ID = t3.SID
   and t3.PADDR = t4.ADDR
   and object_name like tablename

删除锁表

--删除锁表
alter system kill session 'sid,serial'

直接把删除锁表的信息构造查询出来

select 'alter system kill session ''' || t3.SID || ',' || t3.SERIAL# ||
   ''';'
  from v$locked_object t1, dba_objects t2, v$session t3, v$process t4
 where t1.OBJECT_ID = t2.object_id
   and t1.SESSION_ID = t3.SID
   and t3.PADDR = t4.ADDR