[20150821]簡單編寫解除阻塞的指令碼.txt

[20150821]簡單編寫解除阻塞的指令碼.txt

--主要是在rac環境下查殺阻塞的會話，以前的一些指令碼不好用。

SELECT    'alter system kill session '''
         || blocking_session
         || ','
         || blocking_session_serial#
         || ',@'
         || blocking_inst_id
         || ''' immediate;'
            c80, sql_id,machine
    FROM gV$ACTIVE_SESSION_HISTORY
   WHERE     event = 'enq: TX - row lock contention'
         AND sample_time >= SYSDATE - 12 / 86400
GROUP BY blocking_session, blocking_session_serial#, blocking_inst_id,sql_id,machine
  HAVING COUNT (*) >= 10;

--寫成指令碼：
$ cat find_lock.sql
SET PAGESIZE 0
SET ECHO OFF HEADING OFF FEED OFF PAGESIZE 0 VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE;

SET SERVEROUTPUT ON;

BEGIN
   FOR cursor_x
      IN (  SELECT    'alter system kill session '''
                   || blocking_session
                   || ','
                   || blocking_session_serial#
                   || ',@'
                   || blocking_inst_id
                   || ''' immediate'
                      c80
                  ,sql_id
                  ,machine
              FROM GV$ACTIVE_SESSION_HISTORY
             WHERE     event = 'enq: TX - row lock contention'
                   AND sample_time >= SYSDATE - 12 / 86400
          GROUP BY blocking_session
                  ,blocking_session_serial#
                  ,blocking_inst_id
                  ,sql_id
                  ,machine
            HAVING COUNT (*) >= 10)
   LOOP
      EXECUTE IMMEDIATE cursor_x.c80;

      DBMS_OUTPUT.put_line
      (
            SYSDATE
         || ' sql_id='
         || cursor_x.sql_id
         || ' machine='
         || cursor_x.machine
      );
   END LOOP;
END;
/

SET SERVEROUTPUT OFF;
QUIT