1

GRANT ALTER SYSTEM TO <username>;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

-- sys执行

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

1

2

3

4

5

6

7

8

9

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)

);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

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;

/