DB Unload fails with 'ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired' error message

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

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

DB Unload fails to complete and returns errors similar to those below - table names in [brackets] may differ:
 

20170714/213810.400 - U0003592 UCUDB - Status: '' Native error: '54' Msg: 'ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired'
20170714/213810.400 - U0000005 LOCK
20170714/213810.400 - U0037178 Error occured in SQL-Turncate of table [DIVDB].
20170714/213810.400 - U0037107 Error during reorganization of table ['RH']. Program exits with error.
20170714/213810.448 - U0037022 ABORTING due to error.

Investigation

This is a common message that is returned by Oracle in one of the following scenarios:

1) A table is already locked by some query. Example: a "select for update" has been executed and has not yet "committed/rollback" and then a "select query" is sent to the same table.

2) A Database user or another application is running a query that caused a lock on a table.

 

Environment

OS Version: N/A

Cause

Cause type:
Other
Root Cause: Lock on the Database side

Resolution

The recommended actions are:

1. Do a commit/rollback of the locked query and let it complete.
2. If someone (or some app) put the lock on the table, kill the session (contact a DBA if necessary).
3. Change SQLNET.EXPIRE_TIME in SQLNET.ORA file to a maximum of 60 seconds.

Once the lock / locking session has ended, run DB Unload again.

This can also be caused by multiple instances of the utilities running at the same time.  For instance, if the archive is started while the unload is running, the unload will throw this error and end without finishing.  Only run one utility at a time.

Additional Information

Workaround :
N/A