查询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;