Oracle lock table error message...

[omacron]

Hi

This might sound odd but I am trying produce the error message when a table is locked and an update/delete state runs against that table. So what I did was lock a table and then on another terminal do a delete on the table. The problem is when I do in the delete in SQL Plus it doesn't produce an error. It just sits there (left running for 13hours) until the table is released.

Can someone tell me what the error is or tell me how to produce it.

Thanks

 

[SantaMufasa]

Omacron,

Here is the scenario to which you refer:

Session 1:

SQL> update med set staff_id = 'XYZ' where opno = 35452;

1 row updated.

Session 2:

SQL> select * from med where opno = 35452 for update of staff_id nowait;
select * from med where opno = 35452 for update of staff_id nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Notice that to generate an error when one attempts to UPDATE, or DELETE a row that is locked, one must first issue a "SELECT...FOR UPDATE NOWAIT" command, which attempts to lock the row which you want to modify. If the row is lockable, then it locks the row and allows you to continue; if the row is already locked, then Oracle throws and error message that you can handle any way you wish.

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[omacron]

yes that does.