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.