Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle won't release locks - ever!

Re: Oracle won't release locks - ever!

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 4 Jul 2004 18:54:11 -0700
Message-ID: <9711ade0.0407041754.71e13583@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 Received on Sun Jul 04 2004 - 20:54:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US