kill_block_session存储过程创建及使用

1.使用方法

1. 传表名，把访问table的session都kill掉

    exec KILL_BLOCK_SESSION('table');

2. 传job_id，把job相关的session都kill掉

    exec KILL_BLOCK_SESSION(102);

3. 传event_id把等待某event相关的session都kill掉

    exec KILL_BLOCK_SESSION(10);

2.例子：-kill掉job进程

job告警

    : 告警 OracleJobBroken
    告警对象: 192.168.10.11:1521
    告警详情: Oracle job 392 broken  
    应用归属: 
    备注信息: 报表数据库
    DBA   : 
    触发时间: 
    处理方法: 

查看job信息

select job,log_user,schema_user,last_date,last_sec,next_date,next_sec,total_time,broken,interval,failures,what from dba_jobs order by failures desc;

查看job runing

select * from dba_jobs_running;


       SID        JOB   FAILURES LAST_DATE LAST_SEC                         THIS_DATE THIS_SEC                           INSTANCE
---------- ---------- ---------- --------- -------------------------------- --------- -------------------------------- ----------
      1152        392       5509 09-10月-21 09:00:01                         21-10月-21 08:25:17                                  0

已选择 3 行。

kill running job

exec sys.kill_block_session(392)

3.kill_block_session存储创建脚本

----在sys用户下创建kill_block_session

CREATE OR REPLACE PROCEDURE SYS.kill_block_session(p_obj   IN VARCHAR2 DEFAULT NULL,
                                               p_job   IN NUMBER DEFAULT NULL,
                                               p_event IN NUMBER DEFAULT NULL) AS
BEGIN
  IF p_obj IS NOT NULL
     AND p_job IS NULL
     AND p_event IS NULL
  THEN
    FOR c IN (SELECT /*+ rule */
              DISTINCT s.username,
                       s.inst_id,
                       s.sid,
                       s.serial#
                FROM gv$session s,
                     gv$access  a
               WHERE s.inst_id = a.inst_id
                 AND s.sid = a.sid
                 AND a.object = upper(p_obj))
    LOOP
      IF c.username IS NOT NULL
         AND c.username NOT IN ('SYS',
                                'SYSTEM')
      THEN
        dbms_output.put_line('alter system kill session ''' || c.sid || ',' ||
                             c.serial# || ',@' || c.inst_id ||
                             ''' immediate');
        EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' ||
                          c.serial# || ',@' || c.inst_id || ''' immediate';
      ELSE
        raise_application_error(-20000,
                                'Attempting to kill protected system session has been blocked.');
      END IF;
    END LOOP;
  ELSIF p_obj IS NULL
        AND p_job IS NOT NULL
        AND p_event IS NULL
  THEN
    FOR c IN (SELECT /*+ rule */
              DISTINCT s.username,
                       s.inst_id,
                       s.sid,
                       s.serial#
                FROM gv$session s
               WHERE sid IN (SELECT sid
                               FROM dba_jobs_running
                              WHERE job = p_job))
    LOOP
      IF c.username IS NOT NULL
         AND c.username NOT IN ('SYS',
                                'SYSTEM')
      THEN
        dbms_output.put_line('alter system kill session ''' || c.sid || ',' ||
                             c.serial# || ',@' || c.inst_id ||
                             ''' immediate');
        EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' ||
                          c.serial# || ',@' || c.inst_id || ''' immediate';
      ELSE
        raise_application_error(-20000,
                                'Attempting to kill protected system session has been blocked.');
      END IF;
    END LOOP;
  ELSIF p_obj IS NULL
        AND p_job IS NULL
        AND p_event IS NOT NULL
  THEN
    FOR c IN (SELECT /*+ rule */
              DISTINCT s.username,
                       s.inst_id,
                       s.sid,
                       s.serial#
                FROM gv$session s
               WHERE status = 'INACTIVE'
                 AND event# = to_number(p_event))
    LOOP
      IF c.username IS NOT NULL
         AND c.username NOT IN ('SYS',
                                'SYSTEM')
      THEN
        dbms_output.put_line('alter system kill session ''' || c.sid || ',' ||
                             c.serial# || ',@' || c.inst_id ||
                             ''' immediate');
        EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' ||
                          c.serial# || ',@' || c.inst_id || ''' immediate';
      ELSE
        raise_application_error(-20000,
                                'Attempting to kill protected system session has been blocked.');
      END IF;
    END LOOP;
  ELSE
    raise_application_error(-30000,
                            'Wrong number or types of arguments in call to ''kill_block_session''.');
  END IF;
END;
/

----给用户创建同义词

CREATE OR REPLACE SYNONYM VSKYADMIN.KILL_BLOCK_SESSION FOR SYS.KILL_BLOCK_SESSION;

----把执行存储过程权限授予用户

GRANT EXECUTE ON SYS.KILL_BLOCK_SESSION TO VSKYADMIN;