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