Re: How to unlock a locked table without re-starting the instance?

In article <8gqa11$1s7_at_netnews.hinet.net>,   "Dino Hsu" <dino1_at_ms1.hinet.net> wrote:
>
> 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
>
>

Since it's running a report that apparently causes the problem, it's probably not DML_LOCKS and ENQUEUE_RESOURCES set too low, but you can try and increase these by an equal amount if you are also running any parallel DML operations, which require a lot more locks than serial DML operations.

Another thing to look at is whether the locked table is a child table of another table that is frequently subject to updates or deletes. If that is the case, unless there is a foreign key index present, Oracle places a shared lock on the child table every time the parent table is updated. If a foreign key index is present, Oracle uses a pin, a nontransactional  lock instead.

Marion

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 01 2000 - 00:00:00 CDT