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

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 Sun May 28 2000 - 00:00:00 CDT