Re: Strange enqueue problem (even after it is killed!)

In article <88nf0u$gt8$1_at_nnrp1.deja.com>,   robertchung0909_at_my-deja.com wrote:
>
>
> I had one session that locked almost every row in a table. About 10
> minutes ago I killed it. Now, I am trying to lock the same rows, only
> to be put on hold waiting for enqueue lock to be resolved. I took a
> look at the output of utllockt.sql and it clearly showed my new
session
> is waiting for enqueue lock to be released from . . . the session that
> I killed more than 10 minutes ago. By checking locking session's SID
> and looking at v$session, I could verify that it indeed is the killed
> session. What the heck is Oracle doing letting killed session hold
> enqueue lock? Is there something that I can do? 'alter system kill
> session' would not do any good because I've already killed it long
time
> ago. Thank you in advance.
>
> PS: by the way, Oracle's version is 8.0.5 running on Windows NT (my
> boss wanted NT. I only take orders.)
>

Is the serial# column in v$session for the killed session changing every few seconds? If so I believe that it would indicate that pmon is using the original session to perform the rollback. A rollback can take as long as the job ran before it was killed so if the killed session does not disappear in a reasonable amount of time, kill it at the system level if there is a way to do that with NT. Then wait a couple of minutes for pmon to finish the backout.

The above is based on conversations with Oracle support from seeing similiar behavior to what you described. Another thing you should do is check for a pmon trace file and if found look for an 'unable to obtain lock' message. We have had problems in the past where pmon was unable to get some type of internal lock. It would sometimes take pmon 20 - 60 minutes to get the lock and perform backout. If you find this error you will probably want to bounce your instance.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Feb 20 2000 - 10:22:51 CST