Very Computer

My Oracle instance is running on unix

WHen I try .....
alter system kill session ' 596,       21456' ;

The session continues to run...
What else can I do to try to kill this session..?

Thanks TwoplusTwo

Sent via Deja.com
http://www.deja.com/

I'm assuming the extra spaces in what you typed here were a mistake -because
if not, the statement won't work.  It needs to read 'alter system kill
session ' 596,21456' ; with no spaces.  You're supplying a literal string,
after all.

Second, how do you know the session isn't really dead?  What I mean is that
after you issue the kill session command, the affected session won't notice
a thing -until the *next* time it tries to do something as trivial as a
select.  If the session just sits there, it looks like its still connected,
but really it isn't.

Third, test the kill on the session, not by simply re-querying v$session.
I've known v$session to take a good few minutes to realise that a session
has disappeared.  The other session, however, will notice it immediately
(provided it actually tries to do some work).

Since it is Unix, there is always the kill -9 command at the o/s level, but
that's definitely NOT recommended as your first port of call.  Let's see
what's going on with the alter system command before using that as a last
resort.

Let us know how you get on (with screen output if need be).

Regards
HJR

Quote:> My Oracle instance is running on unix

> WHen I try .....
> alter system kill session ' 596,       21456' ;

> The session continues to run...
> What else can I do to try to kill this session..?

> Thanks TwoplusTwo

> Sent via Deja.com
> http://www.deja.com/

Quote:> alter system kill session ' 596,       21456' ;

                                          'sid,session#'  <-- from v$session

Quote:> The session continues to run...

How do you check if the session continues to run? Did you try to query DB
from this session?
The rule is: if the session is not performing any transaction and you kill
it, you can still see this session
listed in v$session view. But when you try to do anything with db from this
killed session, you get ORA-00028' error. At this moment this session is
gone from the view: v$session.

Hth
wysza

sorry for my english

Quote:> My Oracle instance is running on unix

> WHen I try .....
> alter system kill session ' 596,       21456' ;

> The session continues to run...
> What else can I do to try to kill this session..?

> Thanks TwoplusTwo

> Sent via Deja.com
> http://www.deja.com/

Hi guys thanks for the help...
I did try "alter system"  ... with no spaces and I thought the session
was still running because I could still see it in v$session.
Someone pointed out that may not be true.... even thought it looks like
it is connectted, it is not (or not doing any work) right?????

So I guess I can just leave it there...since I neither want to reboot
the instance or want to kill the unix process....

TwoPlusTwo

Sent via Deja.com
http://www.deja.com/

Quote:> Hi guys thanks for the help...
> I did try "alter system"  ... with no spaces and I thought the session
> was still running because I could still see it in v$session.
> Someone pointed out that may not be true.... even thought it looks like
> it is connectted, it is not (or not doing any work) right?????

> So I guess I can just leave it there...since I neither want to reboot
> the instance or want to kill the unix process....

> TwoPlusTwo

Yeah, well.... how about testing that it really *has* worked!!??  I hate to
see DBAs 'guessing' anything, ok!

Get that session to do a simple select: select user from dual would do it.

Until that session reports "Your session has been terminated", you can't
really be sure, can you?

Regards
HJR

I have another instance where it says it cannot complete the kill..
any ideas how to terminate the session....

SQL> alter system kill session '25,23645';
alter system kill session '25,23645'
*
ERROR at line 1:
ORA-00031: session marked for kill

Thanks TwoPlusTwo

Sent via Deja.com
http://www.deja.com/

From the documentation on technet.oracle.com:

If the session is performing some activity that must be completed, such as
waiting for a reply from a remote database or rolling back a transaction,
Oracle waits for this activity to complete, marks the session as dead, and
then returns control to you. If the waiting lasts a minute, Oracle marks the
session to be killed and returns control to you with a message that the
session is marked to be killed. The PMON background process then marks the
session as dead when the activity is complete.

The session you're killing must be in the middle of a big workload!

Regards
HJR