查询oracle被锁对象并解锁

源：http://www.xifenfei.com/607.html

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

SELECT a_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" FROM ALL_OBJECTS A_S,   V$LOCKED_OBJECT V_T,   V$SESSION VN,   V$PROCESS VS WHERE A_S.OBJECT_ID=V_T.OBJECT_ID AND V_T.SESSION_ID =VN.SID AND VS.ADDR=VN.PADDR AND VN.USERNAME NOT IN('SYSMAN','SYS');

2、查询该sid的sql语句

select * from v$sql vl,v$session vn where vl.ADDRESS= decode(vn.SQL_ADDRESS,null,vn.PREV_SQL_ADDR,VN.SQL_ADDRESS) and vn.sid=&sid;

3、解锁

alter system kill session 'sid,serial#'; --note:不能kill自身

4、查询被锁对象增强版

SELECT DDL.OWNER AS 用户,        DDL.NAME  AS 对象,        DDL.type  AS 类型,        VS.OSUSER AS OS_USER,        VS.MACHINE,        VS.STATUS,        VS.PROGRAM,        VS.LOGON_TIME AS "LOGIN_TIME",        VP.SPID,        'kill -9 ' || VP.SPID AS OS_KILL,        vs.sid,        vs.SERIAL#,        'alter system kill session ''' || vs.sid || ',' || vs.serial# ||        ''';' "ORACKE_KILL"   FROM DBA_DDL_LOCKS DDL, V$SESSION VS, V$PROCESS VP  WHERE DDL.SESSION_ID = VS.SID    AND VS.PADDR = VP.ADDR;