Ora-00054: Resource Busy and Acquire with Nowait Specified

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

Your table is already locked by some query. For example, you may have executed "select for update" and have not yet committed/rollbacked and fired another select query. Do a commit/rollback before executing your query.

ORA-00054: resource busy and acquire with NOWAIT in oracle procedure

The only thing in your code that can cause this is the TRUNCATE.

TRUNCATE is not DML. It's a DDL operation. It acquires a full table lock. Because you are doing some DML in other sessions (inserting particular rows), the session performing TRUNCATE cannot acquire the table lock. Somewhere internally NOWAIT is specified, so the exception is raised.

I believe you should reconsider using TRUNCATE here. It is DDL, it performs commit so it's not transactional. Obviously, it is not safe to use it in multi user environment.

You could use TEMPORARY TABLES. All the data you store in them only exists in your current session. This way you don't need to use TRUNCATE.

DDL will look something like this.

CRATE GLOBAL TEMPORARY TABLE TEMP_ACTIVATE_OPTION(
  ID   NUMBER,
  ... -- your columns
)
ON COMMIT DELETE ROWS;

Related Topics