Avoid ORA-00054: resource busy and acquire with NOWAIT specified on Truncate

If you are on an 11g database, you can ignore this post and just use the new ddl_lock_timeout parameter to accomplish what this post explains.

“alter session set ddl_lock_timeout=60” – will effectively allow ddl statements to all wait 60 seconds before encountering the error.

If you are on 10g or lower, here is an option:

If you are an Oracle programmer, then you’ve probably encountered the ORA-00054: resource busy and acquire with NOWAIT specified exception when attempting to truncate a table. When this happens during the day, you can usually just wait a few minutes (or go and commit your other open sessions…), but what about when you are truncating tables as part of a script which runs overnight? Are you tired of being woken up so you can simply rerun the script?

Well check out this script, then! It is a very simple PL/SQL procedure which accepts 1 or 2 arguments.

TABLE_NAME: string containing the table to truncate (include the schema prefix as well).
MAX_WAIT: number which is the maximum amount of minutes you would want to wait (you can ignore this parm and it will default to 10)

The script does the following:

1. Check if it has been running for more than the max wait time – if so, exit with a custom exception.
2. Truncate the table sent as a parameter
2a. If successful – EXIT.
2b. If truncate fails due to ORA-00054 exception, repeat steps 1 & 2 (this is a good place to put dbms_lock.sleep if you desire).
2b. If truncate fails for any other reason, simply pass the exception onto the calling program.

So instead of getting an error when a resource is busy – your code will wait for it to become available, then truncate it as soon as possible. And since you are executing procedures, you can easily use dbms_scheduler to allow your truncates to run concurrently. If you have enough of them, this will be a real time saver. But be careful because if you are doing a truncate and load you will want to have something that checks that the jobs are completed before beginning your load.

Please see the code below:

CREATE OR REPLACE PROCEDURE truncate_tbl(table_name IN VARCHAR2, max_wait IN NUMBER DEFAULT 10)
AS
   ld_start_time   DATE;
BEGIN
   ld_start_time   := SYSDATE;

  <>
   LOOP
      IF (SYSDATE - ld_start_time) < (max_wait/60/24) THEN
         BEGIN
           <>
            EXECUTE IMMEDIATE('TRUNCATE TABLE ' || table_name);

            EXIT truncate_loop;
         EXCEPTION
            WHEN OTHERS THEN
               IF SQLCODE = -54 THEN
                  NULL;
               ELSE
                  RAISE;
               END IF;
         END actual_code;
      ELSE
         RAISE_APPLICATION_ERROR(-20101, max_wait||' Minutes waited for table to be available from NOWAIT status.');
      END IF;
   END LOOP truncate_loop;
END truncate_tbl;

Then you can call it simple with

declare
begin
  truncate_tbl('SCHEMA.TABLE_NAME');
end;
/

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful.