Oracle Procedure gets ORA-00054: resource busy and acquire with NOWAIT

I have a simple procedure. But it sometimes gets ORA-00054 error. I'm suspecting ENABLE PARALLEL DML step. But, I'm not sure if it affects.

After truncating table i insert new data to the truncated table. there is no another operation to lock this table, only this insert statement.

I know truncate is a DDL operation, and it's transactional process on metadata. There is only one chance to lock, truncate and insert step must be start paralelly.

Any ideas?

BEGIN
   -- Enable parallel DML for the session
   EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

   -- Truncate the table
   EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table';

   -- Insert new data into the table with parallel DML enabled
   EXECUTE IMMEDIATE 'INSERT /*+ PARALLEL(my_table, 4) */ INTO my_table (column1, column2)
                       SELECT column1, column2 FROM another_table';

END;

Solution

Try using this code in your procedure:

BEGIN
   -- Temporary change to help debugging:
   -- Let the INSERT statement wait instead of immediately throwing the error
   -- ORA-00054: resource busy and acquire with NOWAIT
   EXECUTE IMMEDIATE 'ALTER SESSION SET ddl_lock_timeout = 1000000';

   -- Truncate the table.
   EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table';

   -- Insert new data into the table with parallel DML enabled.
   EXECUTE IMMEDIATE 'INSERT /*+ ENABLE_PARALLEL_DML APPEND PARALLEL(4) */ INTO my_table (column1, column2)
                       SELECT column1, column2 FROM another_table';

   COMMIT;
END;
/

The next time the session is blocked, it will wait instead of immediately throwing an error. If you can catch the waiting while it's happening, find the blocking session with this query:

-- The session that is blocking your session.
select *
from gv$session
where (inst_id, sid) in
(
    select final_blocking_instance, final_blocking_session
    from gv$session
    where final_blocking_session is not null
);

If the blocking session only lasts a small amount of time, and you cannot catch it happening in real time, you can still probably find the blocker looking through historical performance data. Use the below query to find historical blockers, but you'll need to modify the query to filter out a lot of irrelevant blockers. GV$ACTIVE_SESSION_HISTORY usually has about a day's worth of data in it. If that data is not old enough, replace it with DBA_HIST_ACTIVE_SESS_HISTORY.

select *
from gv$active_session_history
where blocking_session is not null;

There are a lot of subtle issues related to your relatively small piece of code:

Blocking. There is definitely something working on your table at the same time as your process, so one of your assumptions is likely wrong. However, the offending code could be something only indirectly related - for example, a process that is deleting a row from a child table without an indexed foreign key.
Execute immediate. In your sample code, the EXECUTE IMMEDIATE is not necessary, since you know all of the objects are run time. However, in your procedure, you likely do want to keep the dynamic SQL. Your real code uses a procedure, and a procedure with a static reference to a table that is truncated would trigger a recompilation. Code that forces itself to recompile can cause problems, so keep your dynamic SQL.
Hints. ENABLE_PARALLEL_DML can be enabled for a single SQL statement through a hint. You might also want to change PARALLEL(my_table, 4) to PARALLEL(4) - if the statement is going to run in parallel, you might as well parallelize the entire statement instead of just one part of it. The APPEND hint ensures that the statement always runs with direct path inserts. Normally, running the INSERT in parallel will cause direct path mode. But, just in case your server temporarily runs out of parallel sessions, you likely still want to get direct-path writes, so the APPEND may help in some cases.