-If you are authorized to sys user.
Firstly run this procedure to compile and save.
---
CREATE OR REPLACE PROCEDURE P_KILL_SESSION(DATABASE_USER_NAME IN VARCHAR2, STR_RESULT OUT VARCHAR2)
IS
str_exec VARCHAR2(1000);
BEGIN
FOR cur_session IN (SELECT sid, serial#
FROM v$session
WHERE username = upper(DATABASE_USER_NAME)
AND status != 'KILLED') LOOP
str_exec := 'alter system kill session ''' || cur_session.sid || '' || ',' || cur_session.serial# ||'''';
BEGIN
execute immediate str_exec;
STR_RESULT := 'Sessions are terminated successfully';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM ) ;
END;
END LOOP;
END;
---
To use it;
In command window-editor,
var STR_RESULT VARCHAR2
EXEC P_KILL_SESSION('test_user',:STR_RESULT)
--test_user session could be killed
Firstly run this procedure to compile and save.
---
CREATE OR REPLACE PROCEDURE P_KILL_SESSION(DATABASE_USER_NAME IN VARCHAR2, STR_RESULT OUT VARCHAR2)
IS
str_exec VARCHAR2(1000);
BEGIN
FOR cur_session IN (SELECT sid, serial#
FROM v$session
WHERE username = upper(DATABASE_USER_NAME)
AND status != 'KILLED') LOOP
str_exec := 'alter system kill session ''' || cur_session.sid || '' || ',' || cur_session.serial# ||'''';
BEGIN
execute immediate str_exec;
STR_RESULT := 'Sessions are terminated successfully';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM ) ;
END;
END LOOP;
END;
---
To use it;
In command window-editor,
var STR_RESULT VARCHAR2
EXEC P_KILL_SESSION('test_user',:STR_RESULT)
--test_user session could be killed