The solution to the error of [Oracle] ora-00054

Error message:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 
Key words: ORA-00054 lock wait
 
The table cannot be truncated by another process if the table is not committed after the INSERT message is sent.
For example:
Process A creates A new table, inserts A row of data, but does not commit.

ORA-00054: ORA-00054 ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054

The truncate of process B can only be executed after process A commits or after the resource is released.


Note that this situation is different from two processes operating on a resource at the same time (e.g., UPDATE on the same row). When two processes are operating on a resource at the same time, the second operation will get stuck (waiting).
You can find the process that holds the resource, commit the resource and release it. If you cannot commit, you can confirm and kill it.

SQL> select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;


Note that not all the information that appears after using the above statement is the problem process, because normal operation on the table as long as there is a hold resource in the query will be selected, so you can use this command several times to check whether the resource has been held, and you can look at the table name below to help confirm.
Query for sid and serial#.

SQL> select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

KILL the session process.


Read More: