Oracle DB - 異常lock Kill DB session

系統每十分鐘會執行一次檢查lock的cronjob，當收到郵件如下，則需要判斷並處理DB lock。

這時
1. 可以用sqlplus連到DB，用下方sql statement再做一次檢查。



SELECT INST_ID,lpad('',DECODE(request,0,0,1))||sid sess, id1,id2,lmode
,request, type
FROM GV$LOCK
WHERE id1 IN (SELECT id1 FROM GV$LOCK WHERE lmode = 0)
ORDER BY id1,request 


2.  用oracle帳號ssh db server



oracle_DGTEST_testdb1.rgp.raritan.com$ps -ef|grep 7487
oracle    7487     1  0 03:15 ?        00:00:00 oracleDGTEST (LOCAL=NO)
oracle    9201  8414  0 03:43 pts/1    00:00:00 grep 748700


oracle_DGTEST_testdb1.rgp.raritan.com$sqlplus '/ as sysdba'



SQL> select a.sid,a.SERIAL#,a.USER#,a.MACHINE,a.OSUSER from v$session a where a.sid = 197;


       SID    SERIAL#      USER#
---------- ---------- ----------
MACHINE
--------------------------------------------------------------------------------
OSUSER
--------------------------------------------------------------------------------
       197      21999         84
RGP\IT-DAVID-780
david.lu




SQL> alter system kill session '197,21999' immediate;


System altered.


SQL> select a.sid,a.SERIAL#,a.USER#,a.MACHINE,a.OSUSER from v$session a where a.sid = 197;


no rows selected



oracle_DGTEST_testdb1.rgp.raritan.com$ps -ef|grep 7487
oracle    9260  8414  0 03:44 pts/1    00:00:00 grep 7487