Oracle解锁表、包、用户、杀会话、停job

---

一、创建包tzq_server_pkg

sys用户以sysdba身份登录Oracle数据库，创建包 tzq_server_pkg ，上代码：

CREATE OR REPLACE PACKAGE sys.tzq_server_pkg IS

  PROCEDURE unlock_table(table_owner IN VARCHAR2, table_name IN VARCHAR2);

  PROCEDURE unlock_package(package_owner IN VARCHAR2,
                           package_name  IN VARCHAR2);

  PROCEDURE unlock_user(username IN VARCHAR2);

  PROCEDURE stop_job(job_id IN NUMBER);

  PROCEDURE kill_session(se_sid IN NUMBER, se_serail# IN NUMBER);
  PROCEDURE grant_pris(username IN VARCHAR2);
END tzq_server_pkg;
/

CREATE OR REPLACE PACKAGE body SYS.tzq_server_pkg IS

  PROCEDURE unlock_table(table_owner IN VARCHAR2, table_name IN VARCHAR2) IS
    CURSOR c1 IS
      SELECT DISTINCT '''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || '''' AS si_id
        FROM gv$locked_object l
            ,dba_objects      o
            ,gv$session       s
       WHERE l.object_id = o.object_id
         AND l.session_id = s.sid
         AND l.inst_id = s.inst_id
         AND o.owner = upper(table_owner)
         AND o.object_name = upper(table_name);
    c1_rec c1%ROWTYPE;
    v_sql  VARCHAR2(2000);
  BEGIN
    FOR c1_rec IN c1 LOOP
      v_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';
      dbms_output.put_line(v_sql);
      BEGIN
        EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);
      END;
    END LOOP;
  END unlock_table;

  PROCEDURE unlock_package(package_owner IN VARCHAR2,
                           package_name  IN VARCHAR2) IS
    CURSOR c1 IS
      SELECT DISTINCT '''' || a.sid || ',' || a.serial# || ',@' || a.inst_id || '''' AS si_id
        FROM gv$session a
            ,gv$access  b
       WHERE b.object = upper(package_name)
         AND b.owner = upper(package_owner)
         AND a.sid = b.sid
         AND a.inst_id = b.inst_id;
    c1_rec c1%ROWTYPE;
    v_sql  VARCHAR2(2000);
  BEGIN
    FOR c1_rec IN c1 LOOP
      v_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';
      dbms_output.put_line(v_sql);
      BEGIN
        EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);
          NULL;
      END;
    END LOOP;
  END unlock_package;

  PROCEDURE unlock_user(username IN VARCHAR2) IS
    us_name VARCHAR2(200) := username;
    v_sql   VARCHAR2(2000);
  BEGIN
    v_sql := 'alter user ' || us_name || ' account unlock';
    BEGIN
      EXECUTE IMMEDIATE v_sql;
      dbms_output.put_line(us_name || '''s account is unlock');
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
    END;
  END unlock_user;

  PROCEDURE stop_job(job_id IN NUMBER) IS
    CURSOR c1 IS
      SELECT DISTINCT '''' || a.sid || ',' || a.serial# || ',@' || a.inst_id || '''' AS si_id
        FROM gv$session a
            ,(SELECT v.sid
                    ,v.id2     job
                    ,v.inst_id inst_id
                FROM sys.job$ j
                    ,gv$lock  v
               WHERE v.type = 'JQ'
                 AND j.job(+) = v.id2) b
            ,gv$instance c
       WHERE a.inst_id = b.inst_id
         AND a.sid = b.sid
         AND a.inst_id = c.inst_id
         AND c.inst_id = b.inst_id
         AND b.job = job_id;
    c1_rec c1%ROWTYPE;
    v_sql  VARCHAR2(2000);
  BEGIN
    FOR c1_rec IN c1 LOOP
      v_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';
      dbms_output.put_line(v_sql);
      BEGIN
        EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);
          NULL;
      END;
    END LOOP;
  END stop_job;

  PROCEDURE kill_session(se_sid IN NUMBER, se_serail# IN NUMBER) IS
    p_sid           NUMBER := se_sid;
    p_serail        NUMBER := se_serail#;
    is_back_process NUMBER := 0;
    CURSOR c1 IS
      SELECT DISTINCT '''' || a.sid || ',' || a.serial# || ',@' || a.inst_id || '''' AS si_id
        FROM gv$session a
       WHERE a.sid = p_sid
         AND a.serial# = p_serail;
    c1_rec c1%ROWTYPE;
    v_sql  VARCHAR2(2000);
  BEGIN
    IF se_sid IS NULL OR se_serail# IS NULL THEN
      dbms_output.put_line('sid is null or serail# is null');
      RETURN;
    END IF;
    BEGIN
      SELECT 1
        INTO is_back_process
        FROM gv$session
       WHERE sid = se_sid
         AND serial# = se_serail#
         AND TYPE = 'BACKGROUND';
    EXCEPTION
      WHEN OTHERS THEN
        is_back_process := 0;
    END;
    IF is_back_process = 1 THEN
      RETURN;
    END IF;
    FOR c1_rec IN c1 LOOP
      v_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';
      dbms_output.put_line(v_sql);
      BEGIN
        EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);
          NULL;
      END;
    END LOOP;
  END kill_session;

  PROCEDURE grant_pris(username IN VARCHAR2) IS
    us_name VARCHAR2(200) := username;
    v_sql   VARCHAR2(2000);
    v_sql2  VARCHAR2(2000);
    v_sql3  VARCHAR2(2000);
    v_sql4  VARCHAR2(2000);
    v_sql5  VARCHAR2(2000);
    v_sql6  VARCHAR2(2000);
    v_sql7  VARCHAR2(2000);
  BEGIN
    v_sql  := 'grant create synonym,create table,create type,create sequence,create view ,create materialized view,create job,create database link,connect,resource,create procedure ,debug any procedure, debug connect session to ' ||
              us_name;
    v_sql2 := 'grant select on gv_$locked_object to ' || us_name;
    v_sql3 := 'grant select on dba_objects to ' || us_name;
    v_sql4 := 'grant select on gv_$session to ' || us_name;
    v_sql5 := 'grant select on gv_$process to ' || us_name;
    v_sql6 := 'grant select on gv_$sql to ' || us_name;
    v_sql7 := 'grant select on gv_$access  to ' || us_name;
    BEGIN
      EXECUTE IMMEDIATE v_sql;
      EXECUTE IMMEDIATE v_sql2;
      EXECUTE IMMEDIATE v_sql3;
      EXECUTE IMMEDIATE v_sql4;
      EXECUTE IMMEDIATE v_sql5;
      EXECUTE IMMEDIATE v_sql6;
      EXECUTE IMMEDIATE v_sql7;
      dbms_output.put_line('grant success!');
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
    END;
  END grant_pris;

END tzq_server_pkg;
/

二、授权给需要使用的用户log

sys用户以sysdba身份登录Oracle数据库，给需要使用该包（sys.tzq_server_pkg）的用户授予 execute 的权限，执行下面命令授权：

grant execute on sys.tzq_server_pkg to log;

三、解锁表：执行存过unlock_table(schema_name, table_name)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.unlock_table('LOG','tzq_log_t');

四、解锁包：执行存过unlock_package(schema_name, pkg_name)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.unlock_package('LOG','tzq_log_pkg');

五、解锁用户：执行存过unlock_user(username)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.unlock_user('LOG');

六、停止job任务：执行存过stop_job(job_id)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.stop_job(6);

七、杀session会话：执行存过kill_session(se_sid, se_serail#)

7.1、查询需要kill的session的SID及serial#

执行下列SQL：

SELECT * FROM gv$session;

找到你需要kill的那个session会话，拿到SID及serial#：159, 3729

7.2、执行存过kill_session(se_sid, se_serail#)

执行存过kill_session()，kill掉上面的那个session会话。在命令行执行下面的SQL：

set serveroutput on
execute sys.tzq_server_pkg.kill_session(159, 3729);

八、给新建的用户授权：执行存过 grant_pris(username)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.grant_pris('log');