欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Oracle解鎖表、包、用戶、殺會話、停job的方法實現(xiàn)

 更新時間：2023年12月14日 15:22:10   作者：Tzq@2018  
本文主要介紹了Oracle解鎖表、包、用戶、殺會話、停job的方法實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

一、創(chuàng)建包tzq_server_pkg

sys用戶以sysdba身份登錄Oracle數(shù)據(jù)庫，創(chuàng)建包 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數(shù)據(jù)庫，給需要使用該包（sys.tzq_server_pkg）的用戶授予 execute 的權限，執(zhí)行下面命令授權：

grant execute on sys.tzq_server_pkg to log;

在這里插入圖片描述

三、解鎖表：執(zhí)行存過unlock_table(schema_name, table_name)

以上面被授權的log用戶，打開命令行窗口，執(zhí)行下列SQL：

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

在這里插入圖片描述

四、解鎖包：執(zhí)行存過unlock_package(schema_name, pkg_name)

以上面被授權的log用戶，打開命令行窗口，執(zhí)行下列SQL：

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

在這里插入圖片描述

五、解鎖用戶：執(zhí)行存過unlock_user(username)

以上面被授權的log用戶，打開命令行窗口，執(zhí)行下列SQL：

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

在這里插入圖片描述

六、停止job任務：執(zhí)行存過stop_job(job_id)

以上面被授權的log用戶，打開命令行窗口，執(zhí)行下列SQL：

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

在這里插入圖片描述

七、殺session會話：執(zhí)行存過kill_session(se_sid, se_serail#)

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

執(zhí)行下列SQL：

SELECT * FROM gv$session;

找到你需要kill的那個session會話，拿到SID及serial#：159, 3729

在這里插入圖片描述

7.2、執(zhí)行存過kill_session(se_sid, se_serail#)

執(zhí)行存過kill_session()，kill掉上面的那個session會話。在命令行執(zhí)行下面的SQL：

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

在這里插入圖片描述

八、給新建的用戶授權：執(zhí)行存過 grant_pris(username)

以上面被授權的log用戶，打開命令行窗口，執(zhí)行下列SQL：

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

在這里插入圖片描述

 到此這篇關于Oracle解鎖表、包、用戶、殺會話、停job的方法實現(xiàn)的文章就介紹到這了,更多相關Oracle解鎖表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

相關文章

最新評論