OraFAQ Forum: Server Administration » killing all sessions

Home » RDBMS Server » Server Administration » killing all sessions (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

killing all sessions [message #633878]

Thu, 26 February 2015 12:14

me:)
Messages: 3
Registered: November 2014

Junior Member

i have a block of code which is as below , that i am using to kill all sessions, but this is failing as some users have some transactions which are not committed.
the following is a pseudo code

for x in (select sid, serial#, username, status
from v$session se,
v$sql sq
where se.program in ('frmweb.exe','TOAD.exe','crw32.exe','SQL Developer')
and DECODE (se.sql_hash_value, 0, se.prev_hash_value, se.sql_hash_value) = sq.hash_value)

loop
execute immediate 'alter system kill session ''' ||
x.sid || ',' || x.serial# || immediate'''';
dbms_output.put_line( 'Alter session done' );
end loop;

the error i am getting is

ERROR at line 20:
ORA-00031: session marked for kill

is there any way we can force kill the active users without getting the above error ?

Report message to a moderator

Re: killing all sessions [message #633879 is a reply to message #633878]

Thu, 26 February 2015 12:27

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

and STATUS<>'KILLED' ???

Report message to a moderator

Re: killing all sessions [message #633880 is a reply to message #633879]

Thu, 26 February 2015 12:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

00031, 00000, "session marked for kill"
// *Cause:  The session specified in an ALTER SYSTEM KILL SESSION command
//          cannot be killed immediately (because it is rolling back or blocked
//          on a network operation), but it has been marked for kill.  This
//          means it will be killed as soon as possible after its current
//          uninterruptable operation is done.
// *Action: No action is required for the session to be killed, but further
//          executions of the ALTER SYSTEM KILL SESSION command on this session
//          may cause the session to be killed sooner.

Report message to a moderator

Re: killing all sessions [message #633881 is a reply to message #633878]

Thu, 26 February 2015 12:37

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

the error i am getting is

ERROR at line 20:
ORA-00031: session marked for kill

This is not a real error, it just means Oracle does not block you until the session is really killed and returns to you when it continues to kill the session (which means rollback all his current transaction which can last hours) in the background.

Quote:

is there any way we can force kill the active users without getting the above error ?

You can use "disconnect session" instead of "kill session" but this does not change anything about the REAL status of the transactions.
You can also trap this error and ignore it, this is what EXCEPTION block is made for.

The question is what do you want to do this?
Maybe you are not taking the more adequate way to do it.

[Updated on: Thu, 26 February 2015 12:38]

Report message to a moderator

Previous Topic:	Partition Exchange Error On Table With Nested Table
Next Topic:	DBCA not found!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 04 20:05:56 CST 2025