Re: How to unlock a locked table without re-starting the instance?
Date: 2000/05/29
Message-ID: <LllY4.3907$N4.134036_at_ozemail.com.au>#1/1
Hi
You can use the follwing script to check for any kind (Transaction or Object) of locks in your database. If you find that a process is locked by another process you can normally kill the process that is holding the lock. This should clear the locks and allow processing to continue.
rem $Header: lock1.sql,v 1.1 96/11/07 17:48:41 oracle Exp $ rem $Locker: $ rem $Log: lock1.sql,v $
rem Revision 1.1 96/11/07 17:48:41 17:48:41 oracle (Oracle Database Administrator)
rem Initial revision
rem
rem lock.sql - Shows User Lock Information
rem
rem Bruce L. Willden, Oracle Costa Rica
rem 20 Apr 94
rem
set pages 60
prompt User Lock Information
prompt .
Column SID FORMAT 999 heading "Sess|ID " COLUMN OBJECT_NAME FORMAT A17 heading "OBJ NAME or|TRANS_ID" Trunc COLUMN OSUSER FORMAT A10 heading "Op Sys|User ID"COLUMN USERNAME FORMAT A9
COLUMN TERMINAL FORMAT A8 trunc
[Quoted] select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
DECODE(B.ID2, 0, A.OBJECT_NAME,
'Trans-'||to_char(B.ID1)) OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Lock Mode",
DECODE(B.REQUEST,0,' ',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1 and B.SID = C.SID and C.USERNAME is not null
order by B.SID, B.ID2;
Dino Hsu wrote in message <8gqa11$1s7_at_netnews.hinet.net>...
>
>Dear all,
>
>Background information:
>Our retail system is implemented on Oracle 8.05 on Windows NT 4 SP3 (with
>Y2K patch), the programs are developed in Delphi 4. However, this project
is
>an out-sourced one, the vendor knows only Delphi (connection through BDE)
>but nothing about Oracle.
>
>The problem:
>A table called [schema].temp is locked at least for 2 times when the user
is
>running a report, the report has normally been ok, but just these 2 times
of
>pending. When it happens, whoever runs this report will be suspended
>forever. I checked the Instance Manager, no users except me and Oracle are
>active. This table is locked and no more locks can be retrieved at that
>moment. I will receive a message something like 'the resource is locked by
>someone else, you have to use nowait parameter'. Actually it doesn't work
>with nowait parameter, either. Our Oracle DBA, who has 3 years of Oracle
>experience, says a table is not normally locked in this way, and the only
>way to solve this is to re-start the instance (database), and he suggests
to
>do it by stopping and re-starting the instance services. I did it, it
works,
>but I can expect this problem to happen again.
>
>I want to figure out:
>1.What are the possible reasons that cause this problem? Anything related
to
>the application? To the development tools? To the Operations Systems? To
the
>network environment? Or to the database system?
>2.How can we monitor the locking status of a database object? Who's having
>the lock and what kind of lock?
>3.What can I do to solve this problem without re-starting the database?
>
>Any comments will be highly appreciated. Thanks in advance. By the way,
>because I don't usually have time to check the USENET news, please also
>e-mail me copy to Dino Hsu <dino1_at_ms1.hinet.net> so that I know you
replied.
>Thanks again.
>
>Dino
>ITS, Avon Cosmetics Taiwan
>
>
>
Received on Mon May 29 2000 - 00:00:00 CEST