GRANT ALTER SYSTEM TO <username>;

-- sys執(zhí)行
create or replace procedure kill_session
( v_sid number, v_serial number )
as
v_varchar2 varchar2(100);
begin
execute immediate 'ALTER SYSTEM KILL SESSION '''
|| v_sid || ',' || v_serial || '''';
end;
/
 
-- 授權：
grant execute on kill_session to username;
 
-- 普通用戶使用：
exec sys.kill_session(161,14502);

CREATE TABLE action_audit (
id NUMBER GENERATED ALWAYS AS IDENTITY,
operator_name VARCHAR2(50) NOT NULL,
action_time TIMESTAMP NOT NULL,
session_id NUMBER(10) NOT NULL,
serial_id NUMBER(10) NOT NULL,
sql_id VARCHAR2(13),
CONSTRAINT action_audit_pk PRIMARY KEY (id)
);

CREATE OR REPLACE PROCEDURE kill_session (
p_session_id NUMBER,
p_serial_id NUMBER
) AS
v_sql_id VARCHAR2(13);
BEGIN
SELECT s.sql_id INTO v_sql_id FROM v$session s WHERE s.sid = p_session_id AND s.serial# = p_serial_id;
 
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_session_id || ',' || p_serial_id || ''' IMMEDIATE';
INSERT INTO action_audit (
operator_name,
action_time,
session_id,
serial_id,
sql_id
) VALUES (
sys_context('userenv','os_user'),
CURRENT_TIMESTAMP,
p_session_id,
p_serial_id,
v_sql_id
);
 
commit;
 
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Error in killing session: ' || SQLERRM);
END;
/