Killing Oracle Sessions of a User

We are hosting around a hundred websites for the public administration on a web content management system which is built on top of an Oracle database. There is a stage and a live environment with snapshot replication. Sometimes websites on the live environment have to be dropped and recreated. One website is represented by one Oracle user, so in fact the user has to be dropped. This is not possible as long as the webserver holds open sessions.
The following procedure”kill_sessions” terminates all sessions which are connected with a certain username.

CREATE OR REPLACE PACKAGE livelink IS
PROCEDURE kill_sessions (in_username IN VARCHAR2);
PROCEDURE drop_live_site (in_username IN VARCHAR2);
PROCEDURE create_live_site (in_username IN VARCHAR2,
in_url IN VARCHAR2,
in_pl IN VARCHAR2,
in_std IN NUMBER);
PROCEDURE start_repl (in_username IN VARCHAR2);
END livelink;
/

CREATE OR REPLACE PACKAGE BODY livelink IS
PROCEDURE kill_sessions (in_username IN VARCHAR2)
IS
CURSOR sessions_cur (uname VARCHAR2) IS
SELECT TO_CHAR(sid) AS sid,
TO_CHAR(serial#) AS serial#
FROM v$session
WHERE username = uname;
BEGIN
FOR sessions_rec IN sessions_cur(UPPER(in_username))
LOOP
EXECUTE IMMEDIATE ‘ALTER SYSTEM KILL SESSION ”’
|| sessions_rec.sid
|| ‘, ‘ sessions_rec.serial#
|| ””;
END LOOP;
END;
– … some more code here …
END livelink;
/