Oracle 允许特定用户杀死会话的脚本

嗨，

是否有可用的脚本可以杀死会话，但只允许特定用户 (在脚本中预先定义) 执行杀死功能？如果没有，这怎么能执行？

这是我过去在客户端站点上使用过的一个示例。您将 (应该) 根据自己的需要增强/测试/定制脚本

示例使用情况
============
默认情况下，我们报告任何状态为 “活动” 或 “已杀死” 的会话。我们将看到会话详细信息，无论是运行还是阻止，以及SQL ID等。

SQL> select * from table(sys.my_session.s);

Session       User/Elapsed Secs       Current SQL     Status        Program                                 Blocked by Session
--------------------------------------------------------------------------------------------------------------------------------
257,5501      ASKTOM (0)              89uk42w1xkdty   ACTIVE        sqlplus.exe-comcdona
368,43752     ASKTOM (12)             a40p1nyb24j18   ACTIVE        sqlplus.exe-comcdona
复制

或者，我们可以传入 “全部” 以查看所有会话

SQL> select * from table(sys.my_session.s('all'));

Session       User/Elapsed Secs       Current SQL     Status        Program                                 Blocked by Session
--------------------------------------------------------------------------------------------------------------------------------
1,13808       SYS (1769614)                           INACTIVE      VKTM-oracle
2,23469       SYS (1769611)                           INACTIVE      DIAG-oracle
3,36185       SYS (1769611)                           INACTIVE      DBW0-oracle
4,21472       SYS (1769611)                           INACTIVE      SMON-oracle
5,8033        SYS (1769611)                           INACTIVE      PXMN-oracle
6,16680       SYS (1769605)                           INACTIVE      TMON-oracle
7,61493       SYS (1769605)                           INACTIVE      ARC3-oracle
9,16830       APEX_PUBLIC_USER (1122)                 INACTIVE      APEX Listener-oracle
11,3902       SYS (1769590)                           INACTIVE      CJQ0-oracle
12,20631      SYS (1769587)                           INACTIVE      QM00-oracle
14,50003      APEX_LISTENER (949)                     INACTIVE      APEX Listener-oracle
17,2037       SYS (1769585)                           INACTIVE      Q009-oracle
22,41550      SYS (125)                               INACTIVE      W002-oracle
26,52963      ORDS_PUBLIC_USER (99637)                INACTIVE      APEX Listener-oracle
28,27784      SYS (3693)                              INACTIVE      W007-oracle
30,9396       ORDS_PUBLIC_USER (1306)                 INACTIVE      APEX Listener-oracle
119,21406     SYS (1769614)                           INACTIVE      GEN0-oracle
120,9696      SYS (1769611)                           INACTIVE      DBRM-oracle
121,65040     SYS (1769611)                           INACTIVE      LGWR-oracle
122,64828     SYS (1769611)                           INACTIVE      LG01-oracle
123,65400     SYS (1769611)                           INACTIVE      MMON-oracle
...
...
复制

默认情况下，您只需调用 “kill” 例程，我们将查找一个会话，

-已激活超过10秒，
-归你所有，
-在已知用户帐户的列表中
-在你的终端上运行，
-来自sqlplus或sql开发人员，
-不是并行从机

等等。

在这种情况下，我只是dbms_output-ing，但您明白了。我们首先尝试杀死，然后尝试断开连接。


执行系统。我的会话。杀死
更改系统终止会话 “368，43752” 立即
更改系统断开会话 “368，43752”

PL/SQL过程成功完成。


您可以看到这是第一个查询中活动列表中唯一适用的ASKTOM进程。

如果我们找不到会话，或者找不到1个以上的会话，则会出现错误，例如

执行系统。我的会话。杀死
BEGIN my_session.kill; END;

*
ERROR at line 1:
ORA-20000: No suitable session found for killing

执行系统。我的会话。杀死
BEGIN my_session.kill; END;

*
ERROR at line 1:
ORA-20000: More than one session found.  Pass in the SID number
复制

在后一种情况下，您可以进行更具选择性的杀死，例如

执行系统。我的会话。杀死(368)
更改系统终止会话 “368，43752” 立即

PL/SQL过程成功完成。
复制

这是来源

create or replace
package sys.my_session is

  procedure kill(p_sid number default null);

  function s(p_type varchar2 default 'ACTIVE') return sys.odcivarchar2list pipelined;

end;
/

--
-- Choose (wisely) which schemas you want to give access to this
--
grant execute on sys.my_session to ???????;


create or replace
package body sys.my_session is

procedure kill(p_sid number default null) is
  l_sid     int;
  l_serial# int;
  l_user    varchar2(30) := user;
begin
  select s.sid, s.serial#
  into   l_sid, l_serial#
  from   v$session s,
         v$px_session p

  --
  -- Here is where you implement all the rules for controlling exactly *what* sessions
  -- people will be allowed to kill.
  --

  --
  -- must be my own login
  --
  where  s.username = l_user
  --
  -- must be running something for 10 seconds or more
  --
  and    s.status = 'ACTIVE'
  and    s.last_call_et > 10
  --
  -- must be SQL Dev or SQL Plus
  --
  and    ( upper(s.program) like '%SQLPLUS%'
        or upper(s.program) like '%SQL%DEVELOPER%'
         )
  --
  -- must be an account we're allowed to kill (just in case they circumvent the 'own login' check above)
  --
  and   s.username in ('??????','??????')
  --
  -- must be on the same machine as the problem session
  --
  and   upper(s.terminal) = upper(sys_context('USERENV','TERMINAL'))
  --
  -- allow override for explicit sid
  --
  and   s.sid = nvl(p_sid,s.sid)
  --
  -- must not be a parallel slave
  --
  and   s.sid = p.sid(+)
  and   s.serial# = p.serial#(+)
  and   s.sid != p.qcsid(+)
  and   p.sid is null;

  begin
    dbms_output.put_line('alter system kill session '''||l_sid||','||l_serial#||''' immediate');
--    execute immediate 'alter system kill session '''||l_sid||','||l_serial#||''' immediate';
  exception
    when others then null;
  end;

  begin
    dbms_output.put_line('alter system disconnect session '''||l_sid||','||l_serial#||'''');
--    execute immediate 'alter system disconnect session '''||l_sid||','||l_serial#||'''';
  exception
    when others then null;
  end;
  
exception
  --
  -- No session found, means you're fine or trying to be nasty
  --
  when no_data_found then
     raise_application_error(-20000,'No suitable session found for killing');
  --
  -- More than one session found, means we'll need the sid explicitly
  --
  when too_many_rows then
     raise_application_error(-20000,'More than one session found.  Pass in the SID number');
end;

function s(p_type varchar2 default 'ACTIVE') return sys.odcivarchar2list pipelined is
begin
 for i in ( 
    select row_number() over ( order by s.sid)  r, s.sid, s.serial#,
              nvl(s.username,'SYS')
      || ' ('||s.last_call_et||')' username,
      case when s.lockwait is null then
           case when s.username is null then
              nvl2(j.job_sid,'ACTIVE','INACTIVE')
           else
              s.status
           end
      else 'BLOCKED'
      end status,
      nvl(s.sql_id,' ') sql_id,
      nvl(case when s.program is not null then
             ( case when s.program like 'oracle%(%)%' then regexp_substr(s.program,'^oracle.*\((.*)\).*$',1, 1, 'i', 1)
                    else s.program
                    end )
           when s.username is null then ( select p.program
                                          from   v$process p
                                          where  s.PADDR = p.ADDR )
           end || '-' || s.osuser,' ') program ,
           nvl(to_char(blocking_session),' ') blocking_session,
           nvl(case
             when blocking_session is null then cast(null as varchar2(1))
             else
              cast(( select substr(s1.osuser||'-'||s1.program,1,60)
                from   v$session s1
                where s1.sid = s.blocking_session
              ) as varchar2(60))
           end,' ') blocker
    from v$session s,
              ( select sid job_sid
                from   v$lock
                where  type = 'JQ' ) j
    where s.sid = j.job_sid(+)
    and (
          ( upper(p_type) = 'ACTIVE' 
            and s.status in ('ACTIVE','KILLED')
            and ( s.username is not null or ( s.username is null and s.last_call_et < 300 ) )
          ) 
          or
          upper(p_type) = 'ALL'
        )
    order by s.sid
 ) 
 loop
   if i.r = 1 then
     pipe row ( rpad('Session',14)||
                rpad('User/Elapsed Secs',24)||
                rpad('Current SQL',16)||
                rpad('Status',14)||
                rpad('Program',40)||
                rpad('Blocked by Session',20)
               );
     pipe row ( rpad('-',14,'-')||
                rpad('-',24,'-')||
                rpad('-',16,'-')||
                rpad('-',14,'-')||
                rpad('-',40,'-')||
                rpad('-',20,'-')
               );
   end if;
   pipe row ( rpad(i.sid||','||i.serial#,14)||
              rpad(i.username,24)||
              rpad(i.sql_id,16)||
              rpad(i.status,14)||
              rpad(substr(i.program,1,38),40)||
              lpad(i.blocker,20)
            );
 end loop;

end;

end;
/
sho err
select * from table(my_session.s('all'));

复制