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: Mark D Powell <Mark.Powell_at_eds.com>
Date: 3 Jul 2004 11:11:06 -0700
Message-ID: <2687bb95.0407031011.4b9aa4e5@posting.google.com>


Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<9pcde0tcbpil5is7ov06e5iak9lbo8vomo_at_4ax.com>...
> On Sat, 03 Jul 2004 11:18:27 GMT, lfc_at_zoom.co.uk (Leonard F Clark)
> wrote:
>
> >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
> >
> >
>
> Your assertion that bouncing an instance doesn't release locks, is
> ehhmm.. to ridiculous to believe.
> Also there are not TX locks or TM locks to be seen so no transactions
> are involved.
> I think you need to find out what the various locktypes are (they are
> described in the various locking scripts located in
> $ORACLE_HOME/rdbms/admin, which you also didn't use so far)

Along with looking at the scripts Sybrand mentioned you might want to look at the Oracle version# Reference manual entry for v$lock. MR locks are media recovery locks and one exists for every datafile in the database at all times.

HTH -- Mark D Powell -- Received on Sat Jul 03 2004 - 13:11:06 CDT

Original text of this message

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