Re: How to delete a lock

dn.perl_at_gmail.com wrote:
> DA Morgan wrote:
> >
> >
> > Go to www.psoug.org and click on Morgan's Library.
> > Scroll down to Killing Sessions.
> >
> > But before you do anything else run this:
> >
> > SELECT status
> > FROM gv$session
> > WHERE sid = <sid>
> > AND serial# = <serial#>;
> >
> > What is the status?
> >
>
> status = 'ACTIVE' .
>
> I tried to drop the user which was locking the table
> to see what message I get. One function, which looks
> pretty harmless, was supposed to be the culprit. IIRC
> the message talked about a locked function, but I am/was
> not sure whether a function can be locked.

You probably got something like "can't drop connected user." You have to kill off their sessions first.

>
> At any rate, I needed to kill the session quickly, and move
> on, since I had got badly stuck. I will revisit the thread later
> to study it in greater detail.

If you have OEM (or similar 3rd party GUI's), it's real simple, just click on Locks under Instance. Then you can kill off that persons sessions, if necessary, or sometimes even nicely ask if what they are doing is important.

Also, if you have metalink support, there are scripts there too, search the knowledge base for:
locking script

I found those very helpful for being a manly command line person.

I agree with Daniel about the kill -9 on unix, killing the session through ALTER commands or the equivalent OEM command just tells the session to kill itself, which may wait an extremely long time before releasing locks. Just be vewy, vewy, cawefuw.

jg

--
@home.com is bogus.
Are you the keymaster?
http://wvs.topleftpixel.com/archives/photos_skyscapes/060803_1637.shtml