创建PKG，实现kill session



kill session权限是属于DBA权限的，但是不能直接给DBA以外的用户授DBA权限，所以创建一个PKG给用户调用，实现kill session。

create or replace procedure dbmgr.prc_kill_session
(v_username IN varchar2,v_sid IN number ,v_serial#  IN number)
is
   v_cnt     number;
   v_machine varchar2(64);
   v_osuser  varchar2(30);
   v_sql     varchar2(100);
begin
if upper(v_username) is null or upper(v_username) in ('SYS','SYSTEM','DBMGR') then
  dbms_output.put_line('Your username is null or the user can not be killed!');
else
   select count(*) into v_cnt
      from v$session a
      where a.username is not null
      and a.username=upper(trim(v_username))
      and a.sid=v_sid
      and a.serial#=v_serial#;
   if v_cnt = 1 then
      select machine,osuser into v_machine,v_osuser from v$session a
      where a.username is not null
      and a.username=upper(trim(v_username))
      and a.sid=v_sid
      and a.serial#=v_serial#;
      execute immediate 'alter system kill session '''||v_sid||','||v_serial#||'''';
      insert into killuser_log (KILLER,REC_TIME,KILLED_USER,machine,  osuser)
                        values (user,  sysdate, v_username, v_machine,v_osuser);
      commit;
   else
      raise_application_error(-20005,'No such session: username='||v_username||',sid='||v_sid||',serial#='||v_serial#);
   end if;
end if;
end;

/

########################

-- Create table
create table DBMGR.KILLUSER_LOG
(
  KILLER      VARCHAR2(30),
  REC_TIME    DATE,
  KILLED_USER VARCHAR2(30),
  MACHINE     VARCHAR2(64),
  OSUSER      VARCHAR2(30)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );