Check this if you can’t kill a session

by Granville Bonyata on August 24, 2017

Sometimes a session is in v$session, but when you try to kill it you get an ORA-00030 Session Is does not exist.

That session may be from a client that disconnected, and Oracle is now cleaning it up (i.e. rolling back whatever it had done). One way to check is to see if the session’s use of undo blocks is dropping:

 

SELECT s.sid, 
       s.serial#, 
       t.used_ublk used_undo
  FROM gv$session s, 
       gv$transaction t
 WHERE s.saddr = t.ses_addr (+)
   AND s.inst_id = t.inst_id (+)
   AND s.sid = [SESSION # GOES HERE]
/


Run that a few times and see if the used undo blocks keeps dropping.
 

Previous post:

Next post: