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.
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
So did this post answer your question?
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