认真就输

        下面测试开发要求实现会话能KILL自己用户下的会话的功能，由于KILL SESSION默认只有DBA用户才有权限，并且如果授予alter system权限给用户，那么他可以KILL所有用户的会话，不能满足要求，并且alter system包含的权限太多，生产环境不敢授予这样的权限。

1 创建KILL SESSION存储过程

在创建存储过程之前，先确认存储是否存在，如果存在请更换名字

CREATE OR REPLACE PROCEDURE kill_session (pn_sid NUMBER, pn_serial NUMBER)
AS
   lv_user     VARCHAR2 (40);
   user_kill   VARCHAR2 (40);
BEGIN
   SELECT USER INTO user_kill FROM DUAL;

   SELECT username
     INTO lv_user
     FROM v$session
    WHERE sid = pn_sid AND serial# = pn_serial;

   IF lv_user IS NOT NULL AND lv_user = user_kill
   THEN
      EXECUTE IMMEDIATE
         'alter system kill session ''' || pn_sid || ',' || pn_serial || '''';
   ELSE
      raise_application_error (
         -20000,
         'Attempt to kill  other session.');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('SESSION :' || pn_sid || ' IS NOT FIND;');
END;
/

授予相应的权限

grant execute on kill_session to username;
create public synonym kill_session for sys.kill_session;
grant select on v_$session to username;
grant execute on dbms_output to username;
ser serveroutput on

2 KILL SESSION测试

任意输入SID,SERIAL，因为SID不存在，所以会报错
www.htz.pw > exec kill_session(1,1);
SESSION :1 IS NOT FIND;

PL/SQL procedure successfully completed.

会话2执行
www.htz.pw > conn htz/oracle
Connected.
会话1执行
www.htz.pw > exec kill_session(148,70);

PL/SQL procedure successfully completed.
回到会话2执行任意一条SQL语句，提示SESSION已经被KILL
www.htz.pw > select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

下面是测试KILL其它用户的会话
会话2执行 
www.htz.pw > conn htz1/oracle
Connected.
会话1执行 ，这里会报KILL其它会话的提示。
www.htz.pw > exec kill_session(148,72);
BEGIN kill_session(148,72); END;

*
ERROR at line 1:
ORA-20000: Attempt to kill other session.
ORA-06512: at "SYS.KILL_SESSION", line 19
ORA-06512: at line 1