查询ORACLE被锁的对象以及解锁

1、查询oracle被锁对象及其语句

SELECTa_s.owner,

  a_s.object_name,

  a_s.object_type,

  VN.SID,

  VN.SERIAL#,

  VS.SPID"OS_PID",

  VN.PROCESS"CLIENT_PID",

  VN.USERNAME,

  VN.OSUSER,

  VN.MACHINE"HOSTNAME",

  VN.TERMINAL,

  VN.PROGRAM,

  TO_CHAR(VN.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS')"LOGIN_TIME",

  'alter system kill session '''||vn.sid||','||vn.serial#||''';'"ORACKE_KILL",

  'kill -9 '|| VS.SPID"OS_KILL"

FROMALL_OBJECTS A_S,

  V$LOCKED_OBJECT V_T,

  V$SESSION VN,

  V$PROCESS VS

WHEREA_S.OBJECT_ID=V_T.OBJECT_ID

ANDV_T.SESSION_ID =VN.SID

ANDVS.ADDR=VN.PADDR

ANDVN.USERNAME NOTIN('SYSMAN','SYS');

2、查询该sid的sql语句

select* fromv$sql vl,v$session vn

wherevl.ADDRESS= decode(vn.SQL_ADDRESS,null,vn.PREV_SQL_ADDR,VN.SQL_ADDRESS)

andvn.sid=&sid;

3、解锁

altersystem kill session 'sid,serial#';

--note:不能kill自身

4、查询被锁对象增强版

SELECTDDL.OWNER AS用户,

       DDL.NAME AS对象,

       DDL.type AS类型,

       VS.OSUSERASOS_USER,

       VS.MACHINE,

       VS.STATUS,

       VS.PROGRAM,

       VS.LOGON_TIMEAS"LOGIN_TIME",

       VP.SPID,

       'kill -9 '|| VP.SPID ASOS_KILL,

       vs.sid,

       vs.SERIAL#,

       'alter system kill session '''|| vs.sid || ','|| vs.serial# ||

       ''';'"ORACKE_KILL"

  FROMDBA_DDL_LOCKS DDL, V$SESSION VS, V$PROCESS VP

 WHEREDDL.SESSION_ID = VS.SID

   ANDVS.PADDR = VP.ADDR;