ORA-00030, 00000, "user session ID does not exist"

I think it's worth to write a short entry in my blog regards to it. That happened to me some days ago. First time I met this issue.

We were told that a session was blocking another session and preventing it to run a DELETE on one table. Up to this point, everything is normal. I ran an ALTER SYSTEM KILL SESSION.. on the blocking session, and, to my surprise, appeared this message:


ORA-00030 user session ID does not exist



       SID    SERIAL# SQL_ID        EVENT                                BLOCKING_SESSION   MACHINE
---------- ---------- ------------- -------------------------------     ------------------ -------------
       175          1               SQL*Net message from client                             prod2db
       177      36163               EMON slave idle wait                                    prod2db
       179       3105 3avapjm36jj92 SQL*Net message to client                               prod2db
       181       1521 7n102twxm7mz5 enq: TX - row lock contention                       218 IDE0257
       211          7               Streams AQ: qmn coordinator idle wait                   prod2db
       212          1               rdbms ipc message                                       prod2db
       213          1               rdbms ipc message                                       prod2db
       215          5               rdbms ipc message                                       prod2db
       216      17529               EMON slave idle wait                                    prod2db
       218       4560               free buffer waits                                       IDE0257


SQL> alter system disconnect session '218,4560' immediate;
alter system disconnect session '218,4560' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

 Quite shocking, since obviously, the session was there and I could even find the corresponding OS process related to it.

First thing I noticed was the suspicious "free buffer waits" wait in the session. Second, that my system was suffering some memory pressure.

 Next query, showed me that a rollback was in progress:

 PROD2DB SQL>   select ses.sid, ses.serial# , t.used_ublk, ses.event
  2  from v$session ses, v$transaction t
  3  where
  4  t.ses_addr = ses.saddr and
  5* ses.sid = 218



       SID    SERIAL#  USED_UBLK EVENT
---------- ---------- ---------- -------------------
       218       4560      46536 free buffer waits

PROD2DB SQL>



Running this query for some times, could see that USED_UBLK was decreasing.

A bit of investigation showed that is a known issue.  Possibly, the client had already disconnected (it was a SQL Developer session), but the server process was still there. PMON was trying to free the resources, but as the instance had some shortage of resources (have to investigate, the server is under memory pressure and possibly , the instance buffer cache is too small), 

Also, checking at the tracefiles, could see that:

*** SESSION ID:(218.4559) 2016-11-04 11:59:31.199
*** CLIENT ID:() 2016-11-04 11:59:31.199
*** SERVICE NAME:(SYS$USERS) 2016-11-04 11:59:31.199
*** MODULE NAME:(SQL Developer) 2016-11-04 11:59:31.199
*** ACTION NAME:() 2016-11-04 11:59:31.199

opitsk: network error occurred while two-task server trying tosend break; error code = 12592



See how the SERIAL# changed from 4559 to 4560. That's an indication that PMON tries to kill the process. When PMON manage to free some of the process resource, will keep trying to delete the process. When no longer can free up resources (no free buffers), print a message in the trace and will try to delete that process again.

This problem appears when PMON cannot get the needed resources to free up the process. i.e, there are not enough free buffers available to do the cleaning. At this point, the removal of the process is delayed. That's what happened in that case.

Solution: Wait until PMON cleans the session. (that was the solution in my case). Once cleaned it, it disappeared. You can  follow the process and do an estimate about how long it will take using the above query,