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.

3 Responses to “Avoid ORA-00054: resource busy and acquire with NOWAIT specified on Truncate”

  1. Satish Vemuri says:

    Sir,

    In production every week we do data cleansing on operation tables (Staging) and Actual Tables. Data cleansing applies for from current date it’ll keep quarter and 6 months old data in our repository. Rest of data will delete.

    Current procedure is failing due to undo segment on large volume of data deletion. To avoid this, i have come with a solution to delete data on existing table using CTAS for required data and rest will ignore. once interim table is created, i am truncating orginal table and inserting data from interim table to actual table.

    I have tried using online redefinition but didn’t work out for me as per the requirement.

    then i have come with CTAS, everything is fine, but when i do truncate i am getting exception as resource busy with nowait. to avoid this exception thought to keep lock on procedure that perform data inserts on operation tables, but no luck. Then i have started google for a better solution and fortunately found your site, Please help me to complete requirement.

    step1. CREATE TABLE xyz_I nologging as select a.* from XYZ where xyz.trns_date > l_cal_date (L_CAL_DATE is desired from the logic (Quarter and 6 Month from current date) so in this trans_Date will pick date only 6 month either quarter depending on business logic. rest of data will ignore.

    Step2. Once table is created, verifying count and validate in dba_objects for status “Valid” if yes then i am doing truncate table XYZ

    Step3. insert /*+ append */ into xyz select * from xyz_i;

    step4. verify count if the counts are same, then drop interim table.

    Concern is in Step 2. when trying to truncate table getting exception. So i want to avoid exception and continue the below mentioned steps.

    By this i would reach expectation with nodowntime and quick deletion on large volume.

    Regards
    Satish Vemuri

  2. Satish Vemuri says:

    Hi Michael,

    Not yet. Waiting for response. At the same time i am also working on different scenarios. if success will post updates.

    Regards
    Satish Vemuri

Leave a Response