Session marked for kill does not terminate

[dklloyd]

Hope you can help me here...
A session was killed using TOAD. It remained in the session list with a status of KILLED. The process it was running (though some believed it was hanging, hence they killed it) was inserting into a table. As a means of clearing this item I have tried to close the database using imediate but this doesn't do anything as I imagine it is not able to terminate the killed session.
The process won't clear from the session. In v$session the sid=8 & the serial#54306.
Looking through the Forum I have used the command

"alter system kill session '8, 54306';"

This waits a while and returns a message of "session marked for kill".
What else can I do to either kill the session or close the database?

Thanks in advance.

 

[DBAwhosaysNIE]

hey,

this is common, well known, well documented & (at times) extremely irritating behavior. when you tell the instance to kill a session it has to wait for pmon to roll back its uncommitted transactions which can actually take up to 24 hours (usually varies w/size of current transaction). there have been many a thread/TAR on metalink about this and OSS' position is always "that's just how it works".

if you really think the os process has gone off to never-never land (this used to happen to me frequently w/jobs created by Oracle Apps concurrent mgrs) then there's always the nuclear option: kill -9 <proc#>

 

[dklloyd]

Thanks for the response DBAwhosaysNIE!
I've tried the kill -9 option and the process was 1st killed about 2 days ago... Is this a record? So I'm a bit stuck now for things to do.

I've also looked in the v$waitstat which has 1 in the count column of the undo header row. I'm now wondering whether puting one of the offline rollback segs online to see if that helps. Any thoughts on this?

Thanks again... much appreciated.