• Skip to main content
  • Skip to primary sidebar

John 3:30

He Must Increase, I Must Decrease

  • Evangelism Schedule
  • Preaching and Sermons
  • Links
    • OUTREACH GUIDELINES
    • MY TESTIMONY
    • SUPPORT THE MINISTRY
    • THE GOSPEL
    • CONTACT
  • Posts by Category
    • Open Air Preaching
    • Theology
    • Witnessing
    • Just me
    • Memory Verses
    • Creation
    • Movie Reviews
    • Love

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

September 19, 2011 by Michael Coughlin

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.

Filed Under: Just me, Technical Tagged With: Oracle

Reader Interactions

Comments

  1. Satish Vemuri says

    January 31, 2015 at 8:14 am

    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

    • Michael says

      January 31, 2015 at 10:30 am

      So did this post answer your question?

  2. Satish Vemuri says

    February 1, 2015 at 2:34 pm

    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

Primary Sidebar

My Budgeting App

You Need A Budget

The Bible Memory App I Use

The Bible Memory App - Bible Memory Verses

Recent Posts

  • ESV Preaching Bible, Black Goatskin Leather for Sale
  • More Lies and Clickbait Instead of Reporting Facts About Ohio Protests
  • Stimulating Your Thoughts About the Stimulus
  • Evangelism Schedule
  • New Podcast

Tags

2018 OSU abortion Adam apologetics attributes of God Bible catholicism Christ church Courageous Creation discipline end times evangelism Forgiveness glory gluttony God Gospel Grace Hollywood Holy humility Jesus Joy leadership logic Love Mercy Movies Ohio State Open Air Oracle people power prayer preaching pride programming Righteous savior Scripture self-control sin witchcraft

Recent Comments

  • Rusty on TTUN @ tOSU Ministry Report – Nov 24, 2018
  • A(nother) Surprising Work of God » Things Above Us on Nebraska @ tOSU Ministry Report – Nov 3, 2018
  • Mid-October 2018 Presuppositional Apologetics’ Links | The Domain for Truth on Minnesota @ tOSU Ministry Report – Oct 13, 2018
  • mcoughlin on Indiana @ tOSU Ministry Report – Oct 6, 2018
  • Jeff Mardling on Indiana @ tOSU Ministry Report – Oct 6, 2018

Categories

  • Creation
  • Gospel
  • Just me
  • Love
  • Memory Verses
  • memoryfeedmichael
  • Movie Reviews
  • Open Air Preaching
  • Prayer
  • Technical
  • Theology
  • Uncategorized
  • Witnessing
2018 © MichaelCoughlin.net

Copyright © 2023 · Things Above Us on Genesis Framework · WordPress · Log in