查询oracle锁死的文件,【学习笔记】Oracle锁 查询oracle被锁对象及其语句 解决Oracle死锁案例...

天萃荷净

运维DBA反映Oracle某用户会话经常卡死没有反映，经检查为Oracle会话死锁导致，分享一篇关于查询oracle被锁对象及其语句和解决Oracle死锁案例

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、Oracle会话解锁

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;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle锁 查询oracle被锁对象及其语句 解决Oracle死锁案例