Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Application developers may want to kill long-running queries. However, since application developers do not have ALTER SYSTEM privileges, they cannot execute the kill session command. Also, this authorization should not be given to users who are not dba.
There is a way for application developers to kill sessions without ALTER SYSTEM authorization. We may create a procedure to kill sessions and authorize the user to execute them.
Let’s create the following procedure under the SYS user and authorize the user to execute it.
Create Procedure Script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE OR REPLACE PROCEDURE kill_session(prm_sid NUMBER, prm_inst_id NUMBER DEFAULT 1) AS l_user VARCHAR2(30); l_serial NUMBER; l_inst_id NUMBER; BEGIN SELECT t.username, t.SERIAL#, t.INST_ID INTO l_user, l_serial, l_inst_id FROM gv$session t WHERE t.sid = prm_sid AND t.INST_ID = prm_inst_id; IF l_user IS NOT NULL AND l_user NOT IN ('SYS', 'SYSTEM') THEN EXECUTE IMMEDIATE 'alter system kill session ''' || prm_sid || ',' || l_serial || ',@' || prm_inst_id || ''''; dbms_output.put_line(prm_sid || 'Session was killed.'); ELSE raise_application_error(-20000, 'System session cannot be killed'); END IF; END; / |
User Authorization:
1 | grant execute on kill_session to xUser; |
