Home » RDBMS Server » Server Administration » killing all sessions (Oracle 11g)
killing all sessions [message #633878] Thu, 26 February 2015 12:14 Go to next message
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 ?
Re: killing all sessions [message #633879 is a reply to message #633878] Thu, 26 February 2015 12:27 Go to previous messageGo to next message
joy_division
Messages: 4908
Registered: February 2005
Location: East Coast USA
Senior Member
and STATUS<>'KILLED' ???
Re: killing all sessions [message #633880 is a reply to message #633879] Thu, 26 February 2015 12:35 Go to previous messageGo to next message
BlackSwan
Messages: 26275
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.
Re: killing all sessions [message #633881 is a reply to message #633878] Thu, 26 February 2015 12:37 Go to previous message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Thu Nov 15 10:53:41 CST 2018