Oracle解锁表、包、用户、杀会话、停job的方法实现

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;
/

grant execute on sys.tzq_server_pkg to log;

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

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

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

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

select * from gv$session;

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

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