ORACLE中ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源的解決方案

同時對一個表操作,update,truncate 等操作，會造成此錯誤，另外，事物沒有提交，從而進行其他動作也有可能會造成此異常。

ORA-00054 resource busy and acquire with NOWAIT specified

Cause: The NOWAIT keyword forced a return to the command prompt because a resource was unavailable for a LOCK TABLE or SELECT FOR UPDATE command.

Action: Try the command after a few minutes or enter the command without the NOWAIT keyword.

原因:對錶進行相關操作時，該表被鎖定，或表正在被其他程式佔用，導致系統忙。

解決:對錶解鎖或等待完成。

比如當我修改表的某一記錄時候，沒有commit，並又去修改表結構，則會觸發此異常。

異常資訊: ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源

異常demo：

Let's try to produce the problem in our development environment. I have opened two session that connected to database under a schema. In one session, I have created a table and inserted data into it.

SQL> create table a (a number);
內陸運輸
Table created.

SQL> insert into a values(1);

1 row created.

I did not yet committed data in session 1. Now in another session whenever I try to any ddl like (alter table, drop table) ORA-00054 will produce.

In another session,
SQL> alter table a add b number;
alter table a add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> drop table a;
drop table a
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> lock table a in exclusive mode nowait;
lock table a in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Cause of the Problem
Whenever you try to do any structural changes on a table oracle try to lock the table exclusively with NOWAIT option(this is in 10.2g while in 11g you can change the wait timeout). If oracle fails to lock the table exclusively then ORA-00054 will occur.

Solution of the Problem
In 10.2g you are limited to several choices to solve the problem. To avoid it,

-Re run the DDL at a later time when the database become idle.
or,

-Kill the sessions that are preventing the exclusive lock.
or,

-Prevent end user to connect to the database and then run the DDL.

You have different views to see locking information about the table.女裝品牌熱門排行榜
1)DBA_BLOCKERS: Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting. In our scenario this view will not help.

2)DBA_DDL_LOCKS: It lists all DDL locks held in the database and all outstanding requests for a DDL lock.

3)DBA_DML_LOCKS: It lists all DML locks held in the database and all outstanding requests for a DML lock.
If you query from it in the mode_held field you will see 'row exclusive lock'.
SQL> select mode_held from dba_dml_locks where owner='MAXIMSG';

MODE_HELD
-------------
Row-X (SX)

4)DBA_LOCK: It lists all locks or latches held in the database, and all outstanding requests for a lock or latch.

5)DBA_LOCK_INTERNAL: It displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

6)DBA_LOCKS is a synonym for DBA_LOCK.

7)DBA_WAITERS: Shows all the sessions that are waiting for a lock.

In order to see locked object query,