Banglar Tajmahal

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> ALTER TABLE SAID.STFACMAS
ADD (OVRDUE_APP VARCHAR2 (1 BYTE) DEFAULT 'N');

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Reason:

The error message says it all: the table is being used right now, probably by a transaction, so getting an exclusive lock on the table may be next to impossible. Of course, the rows of the table are not locked forever. When sessions perform commit the locks on those rows are released, but before that unlock period gets very far, other sessions may update some other rows of the table—and thus the slice of time to get the exclusive lock on the table vanishes. In a typical business environment, the window for locking the table exclusively does open periodically, but the DBA may not be able to perform the alter command exactly at that time.

Solution

In Oracle Database 11g, there is a better option: the DDL Wait option.

SQL> alter session set ddl_lock_timeout = 10;

Session altered.

Now, when a DDL statement in the session does not get the exclusive lock, it will not error out.
Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation
until it's successful or the time expires, whichever comes first.

SQL> ALTER TABLE SAID.STFACMAS
ADD (OVRDUE_APP VARCHAR2 (1 BYTE) DEFAULT 'N');

Error: Package: oracleasmlib-2.0.12-1.el7.x86_64 (/oracleasmlib-2.0.12-1.el7.x86_64)

Error

[root@oracle-rac-node1 yum]# yum install oracleasm*
Loaded plugins: langpacks, product-id, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Examining oracleasmlib-2.0.12-1.el7.x86_64.rpm: oracleasmlib-2.0.12-1.el7.x86_64
Marking oracleasmlib-2.0.12-1.el7.x86_64.rpm to be installed
Examining oracleasm-support-2.1.8-3.el7.x86_64.rpm: oracleasm-support-2.1.8-3.el7.x86_64
Marking oracleasm-support-2.1.8-3.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracleasm-support.x86_64 0:2.1.8-3.el7 will be installed
---> Package oracleasmlib.x86_64 0:2.0.12-1.el7 will be installed
--> Processing Dependency: oracleasm >= 1.0.4 for package: oracleasmlib-2.0.12-1.el7.x86_64
--> Finished Dependency Resolution
Error: Package: oracleasmlib-2.0.12-1.el7.x86_64 (/oracleasmlib-2.0.12-1.el7.x86_64)
           Requires: oracleasm >= 1.0.4
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Solution

[root@oracle-rac-node1 yum]# rpm -ivh --nodeps --force oracleasmlib-2.0.12-1.el7.x86_64.rpm
warning: oracleasmlib-2.0.12-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracleasmlib-2.0.12-1.el7        ################################# [100%]
[root@oracle-rac-node1 yum]# rpm -ivh --nodeps --force oracleasm-support-2.1.8-3.el7.x86_64.rpm
warning: oracleasm-support-2.1.8-3.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracleasm-support-2.1.8-3.el7    ################################# [100%]
Note: Forwarding request to 'systemctl enable oracleasm.service'.
ln -s '/usr/lib/systemd/system/oracleasm.service' '/etc/systemd/system/multi-user.target.wants/oracleasm.service'
[root@oracle-rac-node1 yum]#