oracle笔记_1_查询死锁与解锁

锁表查询

SELECT object_name, machine, s.sid, s.serial#
FROM V$locked_object l, dba_objects o, V$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid;

SELECT * FROM V$SESSION T1, V$LOCKED_OBJECT T2
  WHERE T1.SID = T2.SESSION_ID;

解锁

--释放SESSION
--alter system kill session 'sid, serial#';
ALTER system kill session '23, 1647';

查询死锁的存储过程

select b.sid, b.SERIAL#, a.OBJECT, 'alter system kill session ' || '''' || b.sid || ',' ||b.SERIAL# ||''';' kill_command
  from SYS.V_$ACCESS a, SYS.V_$session b
  where a.type = 'PROCEDURE'
    and (a.OBJECT like upper('%sp_RCM_RiskCompute_Cust%') or　　--存储过程名
      a.OBJECT like lower('%sp_RCM_RiskCompute_Cust%'))
    and a.sid = b.sid
    and b.status = 'ACTIVE';