DB 세션 Auto Kill Script

sqlplus "/as sysdba" << !

set serveroutput on
alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';

column dcol new_value dbname noprint
select INSTANCE_NAME DCOL from V\$INSTANCE;

set heading off
spool /SCRIPT/DB/KILL/lock_kill_&dbname..log

select 'Process:'||D.SPID
  from V\$LOCK A, V\$SESSION_WAIT B, V\$SESSION C, V\$PROCESS D,
       (select SID, max(CTIME) from V\$LOCK where (ID1, ID2, type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0) group by SID) E
 where (A.ID1, A.ID2, A.type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0)
   and A.CTIME > 1800
   and A.SID = B.SID
   and A.SID = C.SID
   and A.SID = E.SID
   and A.REQUEST = 0
   and A.TYPE = 'TX'
   and C.PADDR = D.ADDR
   and B.EVENT = 'SQL*Net message from client'
   and C.USERNAME not in ('SYS', 'SYSTEM')
   and C.STATUS = 'INACTIVE';

spool off


declare
  KILL_SQL    varchar2(100);
  STATUS_SQL  varchar2(100);

  cursor GREP_SESSION is
    select A.SID, C.SERIAL#, D.SPID, C.USERNAME, C.OSUSER, C.MACHINE, C.PROGRAM, A.CTIME, C.LOGON_TIME, B.EVENT
      from V\$LOCK A, V\$SESSION_WAIT B, V\$SESSION C, V\$PROCESS D,
           (select SID, max(CTIME) from V\$LOCK where (ID1, ID2, type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0) group by SID) E
     where (A.ID1, A.ID2, A.type) in (select ID1, ID2, type from V\$LOCK where REQUEST > 0)
       and A.CTIME > 1800
       and A.SID = B.SID
       and A.SID = C.SID
       and A.SID = E.SID
       and A.REQUEST = 0
       and A.TYPE = 'TX'
       and C.PADDR = D.ADDR
       and B.EVENT = 'SQL*Net message from client'
       and C.USERNAME not in ('SYS','SYSTEM')
       and C.STATUS = 'INACTIVE';

begin
  dbms_output.enable(1000000);

  for R_TABS in GREP_SESSION
  loop
    KILL_SQL := 'alter system kill session ' || CHR(39) || '' || R_TABS.SID || ',' || R_TABS.SERIAL# || '' || CHR(39);

    DBMS_OUTPUT.PUT_LINE( CHR(10) );
    DBMS_OUTPUT.PUT_LINE( ' * Current Time       : ' || SYSDATE );
    DBMS_OUTPUT.PUT_LINE( ' * Holder Logon Time  : ' || R_TABS.LOGON_TIME );
    DBMS_OUTPUT.PUT_LINE( ' * SQL Execution Time : ' || ROUND(R_TABS.CTIME/60,2) || ' (Min)' );
    DBMS_OUTPUT.PUT_LINE( ' * OS Process         : ' || R_TABS.SPID );
    DBMS_OUTPUT.PUT_LINE( ' * DB Session         : ' || R_TABS.SID );
    DBMS_OUTPUT.PUT_LINE( ' * Serial#            : ' || R_TABS.SERIAL# );
    DBMS_OUTPUT.PUT_LINE( ' * OS User            : ' || R_TABS.OSUSER );
    DBMS_OUTPUT.PUT_LINE( ' * DB Username        : ' || R_TABS.USERNAME );
    DBMS_OUTPUT.PUT_LINE( ' * Machine            : ' || R_TABS.MACHINE );
    DBMS_OUTPUT.PUT_LINE( ' * Program            : ' || R_TABS.PROGRAM );
    DBMS_OUTPUT.PUT_LINE( ' * Holder Wait Event  : ' || R_TABS.EVENT );
    DBMS_OUTPUT.PUT_LINE( CHR(10) );

    DBMS_OUTPUT.PUT_LINE(KILL_SQL);

    execute immediate KILL_SQL;

    select STATUS into STATUS_SQL from V\$SESSION where SID = R_TABS.SID;

    DBMS_OUTPUT.PUT_LINE( CHR(10) );
    DBMS_OUTPUT.PUT_LINE( ' * Current Time       : ' || SYSDATE );
    DBMS_OUTPUT.PUT_LINE( ' * Status after Kill  : ' || STATUS_SQL );
    DBMS_OUTPUT.PUT_LINE( CHR(10) );

  end loop;
end;
/

!

cd /SCRIPT/DB/KILL

process=`cat lock_kill_${ORACLE_SID}.log | grep '^Process:' | grep -v ora_ | cut -d : -f2`
confirm_process=`ps -ef | grep oracle | grep ${ORACLE_SID} | grep $process`

if [ $? -eq 0 ]
then
  echo '\nProcess $process will be killed ... \n'
  kill -9 $process
fi

rm /SCRIPT/DB/KILL/lock_kill_${ORACLE_SID}.log