Oracle Lock info In RAC .....
for session id in sqlplus
col sid format a12
select sys_context('USERENV','SID') as sid from dual
====================================================
col Kill_Command format a35 SELECT gvh.inst_id Locking_Inst, gvh.SID Locking_Sid, gvs.serial# Locking_Serial, gvs.status Status, -- gvs.module Module, gvw.inst_id Waiting_Inst, gvw.SID Waiter_Sid, DECODE(gvh.TYPE, 'MR', 'Media_recovery', 'RT', 'Redo_thread', 'UN', 'User_name', 'TX', 'Transaction', 'TM', 'Dml', 'UL', 'PLSQL User_lock', 'DX', 'Distrted_Transaxion', 'CF', 'Control_file', 'IS', 'Instance_state', 'FS', 'File_set', 'IR', 'Instance_recovery', 'ST', 'Diskspace Transaction', 'IV', 'Libcache_invalidation', 'LS', 'LogStaartORswitch', 'RW', 'Row_wait', 'SQ', 'Sequence_no', 'TE', 'Extend_table', 'TT', 'Temp_table', 'Nothing-') Waiter_Lock_Type, DECODE(gvw.request, 0, 'None', 1, 'NoLock', 2, 'Row-Share', 3, 'Row-Exclusive', 4, 'Share-Table', 5, 'Share-Row-Exclusive', 6, 'Exclusive', 'Nothing-') Waiter_Mode_Req , 'alter system kill session '|| '''' || gvh.SID || ',' || gvs.serial# || ''' immediate;' "Kill_Command" FROM gv$lock gvh, gv$lock gvw, gv$session gvs WHERE (gvh.id1, gvh.id2) IN ( SELECT id1, id2 FROM gv$lock WHERE request=0 INTERSECT SELECT id1, id2 FROM gv$lock WHERE lmode=0 ) AND gvh.id1=gvw.id1 AND gvh.id2=gvw.id2 AND gvh.request=0 AND gvw.lmode=0 AND gvh.SID=gvs.SID AND gvh.inst_id=gvs.inst_id ;
LOCKING_INST LOCKING_SID LOCKING_SERIAL STATUS WAITING_INST WAITER_SID WAITER_LOCK_TYPE WAITER_MODE_REQ Kill_Command ------------ ----------- -------------- -------- ------------ ---------- --------------------- ------------------- ----------------------------------- 1 17 5 INACTIVE 1 37 Transaction Exclusive alter system kill session '17,5' im mediate;
No comments:
Post a Comment