Re: "lock table in exclusive mode"

This is a problem with the program logic of the other session. If a session issues a DML and the resource is busy, the statement will wait until the necessary locks are available before proceeding. The other session should not be assuming that the locks it needs are always going to be available, instead it should attempt to get the appropriate locks (e.g. LOCK, or SELECT ... FOR UPDATE NOWAIT) with appropriate error handling if they are not available. By using NOWAIT, your other session retains control of its own actions and can be programmed to pause, loop and try again a certain number of times or give up and issue its own error message, or even get on with some other task and come back to try this failed task again later. The worst thing for it to do is simply hang there waiting for a lock with a silly grin on its face.

Martin Doherty

charlie s wrote:

> I am using Oracle817.
> I am developping a script in which I want to
> 1) lock the table
> 2) refresh the materialized view
> 3) drop the table
>
> The problem is, after I lock the table in exclusive mode, if there is
> another session trying to do DML on the table, the session will be hanging
> there, as soon as I issued "drop table", the lock is released, the I will
> get an error
> ERROR at line 1:
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> How can I lock the table so that other session will get an error if they try
> to do DML on the table instead of waiting there?
>
> Thanks for your help