Re: Oracle won't release locks - ever!

fitzjarrell_at_cox.net (David Fitzjarrell) wrote in message news:<9711ade0.0407041754.71e13583_at_posting.google.com>...
> lfc_at_zoom.co.uk (Leonard F Clark) wrote in message news:<40e6928c.10397125_at_usenet.plus.net>...
> > I'm running Oracle 9R2 on several machines (2 Windows 2003 Server and
> > one Windows XP Pro). I've had to kill a batch process running on each
> > and, inevitably, it's left locks for quite some time as the jobs roll
> > back.
> >
> > However, on all machines these locks are not being released. (It
> > looks as if the rollback is just not ... er ... rolling back.)
> >
> > Most of the locks are on the data files. There's also the rollback
> > and a lock for creating a new block. None of them are showing as
> > blockers. I have tried shutting down the instance and even re-booting
> > the server but the locks remain (although it starts CTIME from 0
> > again.
> >
> > Query results:
> >
> > from select * from v$lock;
> >
> > ADDR KADDR SID TY ID1 ID2 LMODE
> > REQUEST CTIME BLOCK
> > -------- -------- ---------- -- ---------- ---------- ----------
> > ---------- ---------- ----------
> > 2F2FD590 2F2FD5A0 2 MR 201 0 4
> > 0 56535 0
> > 2F2FD544 2F2FD554 2 MR 18 0 4
> > 0 56535 0
> > 2F2FD4F8 2F2FD508 2 MR 17 0 4
> > 0 56535 0
> > 2F2FD4AC 2F2FD4BC 2 MR 16 0 4
> > 0 56535 0
> > 2F2FD460 2F2FD470 2 MR 15 0 4
> > 0 56535 0
> > 2F2FD414 2F2FD424 2 MR 14 0 4
> > 0 56535 0
> > 2F2FD3C8 2F2FD3D8 2 MR 12 0 4
> > 0 56535 0
> > 2F2FD37C 2F2FD38C 2 MR 11 0 4
> > 0 56535 0
> > 2F2FD330 2F2FD340 2 MR 10 0 4
> > 0 56535 0
> > 2F2FD2E4 2F2FD2F4 2 MR 9 0 4
> > 0 56535 0
> > 2F2FD298 2F2FD2A8 2 MR 8 0 4
> > 0 56535 0
> > 2F2FD24C 2F2FD25C 2 MR 7 0 4
> > 0 56535 0
> > 2F2FD200 2F2FD210 2 MR 6 0 4
> > 0 56535 0
> > 2F2FD1B4 2F2FD1C4 2 MR 5 0 4
> > 0 56535 0
> > 2F2FD168 2F2FD178 2 MR 4 0 4
> > 0 56535 0
> > 2F2FD11C 2F2FD12C 2 MR 3 0 4
> > 0 56535 0
> > 2F2FD0D0 2F2FD0E0 2 MR 2 0 4
> > 0 56535 0
> > 2F2FD084 2F2FD094 2 MR 1 0 4
> > 0 56535 0
> > 2F2FCFA0 2F2FCFB0 3 RT 1 0 6
> > 0 56538 0
> > 2F2FCE70 2F2FCE80 4 XR 4 0 1
> > 0 56540 0
> > 2F2FCFEC 2F2FCFFC 5 TS 2 1 3
> > 0 56534 0
> >
> > 21 rows selected.
> >
> > Other queries:
> >
> > SQL> select * from v$locked_object;
> >
> > no rows selected
> >
> > SQL> select * from v$transaction;
> >
> > no rows selected
> >
> > I'm really stymied by this one. Inevitably, when I try re-running the
> > batch, I get ORA-00600 errors.
> >
> > Any suggestions greatly recieved
>
> What you have is a listing of the enqueues Oracle establishes to
> serialize access to various database resources. The MR (Media
> Recovery), RT (Redo Thread) and TS (Temporary Segment) enqueues are
> always present, else the various resources would not be available (as
> Nuno Souto said in another post). Such locks are nothing to be
> worried about. Excessive waits for these resources would be a cause
> for concern; since you've not mentioned any issues with excessive
> enqueue waits I see no reason for producing any additional grey hair
> worrying.
>
> The ORA-00600 errors, however, ARE something to be worried about.
> Perhaps you can use the Metalink ORA-00600 lookup interface:
>
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=153788.1
>
> (to provide information on the specific ORA-00600 errors you are
> experiencing); this requires the first argument supplied after the
> ORA-00600 error number. If you receive no information from that
> interface, and a search of Metalink for the ORA-00600 error strings
> you're seeing also reveals nothing then a TAR should be opened with
> Oracle to address the issue.
>
> The assistancs you received from omlet_at_omlet.org stating:
>
> > you have corrupted blocks in those data files that Oracle is trying to
> > recover.
> > Dump the blocks 56535, .., 56540 and shove them up Daniel ass! He
> > would not know his grop from prog and still he is taking the space to
> > answer with his hog wash.
>
> is truly hogwash, as there are no block ids listed in the V$LOCK view;
> apparently he has his CTIME values confused with actual block id
> values.
>
> I'd stop worrying about these locks; they are a normal part of proper
> Oracle operation.
>
> David Fitzjarrell

Dump the blocks (derived from the act of blocking -- not grouping but plucking) 56535,..., 56545 and shove them up your ass!

PS. Learn to read between words you sip of a piss; big jumble of Q

Someone tells you that locks are never released and you tell them this is normal operation?! Because Oracle started up in fast startup mode?! Enqueues are like your mother; they are in short supply!?

Do not take this post as an answer! Your mother actually living in the dreams of so many; that I would not compare her to enqueues -- is not the def of SOB. If you notice I added few more blocks because you are now in the 2nd grade. Received on Tue Jul 06 2004 - 02:10:51 CDT