What happens when a session is killed?
January 18th, 2006 By gamyers
When a session is killed by an ALTER SYSTEM KILL SESSION 'nnnn,nnnn', it won’t necessarily vanish immediately. If the session has made changes to the database, they have to be undone just as if you had coded a ROLLBACK. Drastic action, such as a forced reboot of the database, may make the killed session vanish, but the rollback still has to be done.
Generally it is best to leave the client program, such as TOAD or SQL*Plus, running until the rollback is complete. That way, the client will receive the ‘Your session has been killed’ error and the database session can exit cleanly.
You can monitor how much work a session has still to rollback using an SQL like this :
SELECT
vt.used_ublk ,
vs.sid,
vs.serial#,
vs.username,
vs.status,
vs.schemaname,
vs.osuser,
vs.machine,
vs.terminal,
vs.program,
vs.prev_hash_value,
vs.sql_hash_value,
vt.start_ubablk,
used_urec
FROM v$session vs, v$transaction vt, v$sqlarea a
WHERE vs.taddr = vt.addr
AND bitand(vt.flag,POWER(2,7))> 0
AND a.hash_value(+) =
DECODE(vs.sql_hash_value,
0,vs.prev_hash_value,
vs.sql_hash_value)
The USED_UREC should steadily decrease until the rollback is complete.
January 18th, 2006 at 11:08 am
Nice script.
Here is a script I use to log users off the database. I could combine the two and it might be a nicer script! :)
ALTER SYSTEM KILL SESSION
(SELECT s.sid,
s.serial#
FROM v$session s
WHERE UPPER(s.username) = UPPER(’SYSTEM’)
/
create or replace procedure set_udump( p_udump in varchar2 )
2 as
3 begin
4 execute immediate ‘alter system set user_dump_dest = ”’ || p_udump
|| ”’ scope=memory’;
5 end;
6 /
GRANT ALTER SYSTEM TO someuser;
GRANT SELECT ON v$_session to someone;
CREATE OR REPLACE PROCEDURE drop_user
(p_drop_user IN VARCHAR2)
AS
v1_sid number;
v1_serial number;
CURSOR C1
IS
SELECT s.sid,
s.serial#
FROM v$session s
WHERE UPPER(s.username) = UPPER(p_drop_user);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO v1_sid, v1_serial;
EXIT WHEN C1%NOTFOUND;
EXECUTE IMMEDIATE ‘ALTER SYSTEM KILL SESSION ”’ || v1_sid || ‘, ‘ || v1_serial || ””;
END LOOP;
CLOSE C1;
COMMIT;
END;
January 23rd, 2006 at 8:01 am
One thing to watch for - the killed session receives a “Your session has been killed” error. It is possible to trap that error in PL/SQL (although, obviously, not to do anything particularly meaningful with it…)
Badly-written PL/SQL, which uses
can therefore end up in an infinite loop when killed.
February 6th, 2006 at 10:00 am
In some circumstances you don’t want your killed sessions to hang around there until restart. For example in our application we had a restriction that a user can only open 2 sessions to the database and these zombies prevented a user from logging on. If you want to make sure the session you are killing actually keels over instantly do not issue ‘kill session’. Instead get PID of the process (on UNIX) from v$process.spid (join to v$session to get your session) and kill the process first using OS kill command. Then issue ‘kill session’.
Vadim Bobrov