需求

----Oracle授予普通用户kill session权限
----可以kill普通用户的会话
----无权kill后台进程会话和sys\system用户会话
----适用于单机或rac

1. 创建存储过程

----sys用户下执行

create or replace procedure p_kill_session
( v_sid number, v_serial number )
as
v_varchar2 varchar2(100);
begin
if v_sid is not null and v_serial is not null then
    for c in (select /*+ rule */
               distinct s.username, s.inst_id, s.sid, s.serial#
                from gv$session s
               where 
                s.sid = v_sid
                 and s.serial# = v_serial) 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;

2. 授权：

grant execute on p_kill_session to apm;
 
CREATE OR REPLACE SYNONYM apm.p_kill_session FOR SYS.p_kill_session;

3. 普通用户使用：

begin
 p_kill_session(sid,serial#);
end;

例子：

begin
p_kill_session(102,1102);
end;