luni, 16 septembrie 2013

I query the locking sessions, i see all of them, but i can't kill some of them because of ORA-00030:User Session ID does not exist (RAC database)

First of all, let's see the corect syntax:

alter system kill session '393','17149' immediate;

Pay attention, the syntax can be tricky sometimes.
Let's see, also, an example of locking sesssions hunter script:

select a.inst_id,a.sid,a.serial# serial1,a.sql_id,a.event,a.seconds_in_wait,
a.blocking_session,b.serial# serial2,a.status,a.state 
from gv$session a, 
(select sid,serial# from gv$session) b where wait_class not in 'Idle' and seconds_in_wait>10 and a.blocking_session=b.sid order by seconds_in_wait;

2 393 17149 aaxj0qygjyw8f enq: TX - row lock contention 968915 177 19301 ACTIVE WAITING
2 415 7759 aaxj0qygjyw8f enq: TX - row lock contention 969799 177 19301 ACTIVE WAITING
2 175 8825 aaxj0qygjyw8f enq: TX - row lock contention 970125 177 19301 ACTIVE WAITING

You can change the value of 10 seconds as you wish. As you can notice, the script was executed against a RAC architecture, the gv$session object states this.

Now:

alter system kill session '177,19301' immediate;

ORA-00030:User Session ID does not exist

Oops! How that so?

Solution: 99,99% the cause is we are logged on the wrong instance. Oracle doesn't let you to kill a session asigned to an instance, being logged on another instance. This is because the kill session statement has to do with kill -9 process from UNIX or orakill from Windows.
So: log on the right instance (the first column from that sql  tells you the wich it is) and the command will work. Be aware, the locking sessions sql does not tell anything about the instance of the locking session, just about the instance of the locked ones. So, you have to query the gv$session again, with the SID of the locking session in your hand.