Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, surya.

Asked: November 25, 2015 - 5:27 am UTC

Last updated: November 25, 2015 - 1:18 pm UTC

Version: 10G

Viewed 1000+ times

You Asked

Hi,

We've a pl/sql running on our application. In order to kill the pl/sql we are giving the below command.

ALTER SYSTEM KILL SESSION 'sid,sessionid' to be executed immediately.

However, when this command is executed , it doesnt return the status code ora28 or any message saying that the session has been
killed.

I've checked v$session with the SID and the status says inactive.

After some time , the entry on the v$session ,v$process is cleared.However , the process still goes on ,status is not returned back.

Please help me analyse on what needs to be done next to make sure the process/session is killed and mostly importantly the status is returned
back saying the session has been killed .

and Connor said...

A standard 'alter system kill session' is really you *asking* the session to die. If that session is totally blocked from even accepting such a request, then you will wait (which is why you sometimes see "session marked for kill")

Even if we respond immediate, there will never be a *guarantee* that we can immediately free up a session and the resources it was consuming. For example, what if that session has modified 1,000,000 rows. Even if we kill that session, it will have locks on those rows until all of those changes can be rolled back in the background. (You can see this occurring by watching USED_UREC in V$TRANSACTION)

Check out some of the variations for ending sessions in the docs:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#BABEBJFA

and explore which ones get *closest* to what you want.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

the link is broken

Rajeshwaran, Jeyabal, November 25, 2015 - 12:36 pm UTC

Connor - the link provided seem to be broken, returned 404 error.
Connor McDonald
November 25, 2015 - 1:18 pm UTC

Sorry - its an AskTom bug, it removes "#" from links in certain cases.

Place a "#" after the htm