联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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;