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