Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: April 10, 2017 - 11:51 am UTC

Last updated: May 06, 2019 - 4:37 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,
i tried to load data into a table using SQL Loader
in Control File i used TRUNCATE Statement. and deleted the Data In that Table using Delete Statement(i did not Commit) before Loading.
When i run the SQL Loader Getting Below Error.
SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCA
TE keyword) for table T1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

When i Executed COMMIT Data Loaded Succesfully
Why its not loaded when i did not Execute COMMIT.

and Connor said...

"truncate" is DDL, so it needs to get an exclusive lock on the table before it can run. Your uncommitted delete stopped that from being possible. It is not a SQL Loader issue.

eg

--
-- Session 1
--
SQL> create table t ( x int );

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete t;

1 row deleted.

--
-- Session 2
--
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
</code>


You could consider setting "ddl_lock_timeout" to some suitable value to mitigate against this

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 11, 2017 - 3:52 am UTC

I am not familiar with LOCK Concept can you Explain Briefly.

LOCK concept

Rajeshwaran, April 11, 2017 - 8:16 am UTC

The Concepts guide, cover them nicely.

http://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1331

Slightly different problem than this

AmanJ, May 06, 2019 - 9:27 am UTC

In my case, control looks like
TRUNCATE
INTO TABLE LOAD_TABLE

but the difference is these are all called using script.
So after execution of ctl file, .sql file gets called where the code is like.

if (some_condition) then
truncate table LOAD_TABLE;
update something;
commit;
else
update something_else;
commit;
end if;

After completion of above process, I can see this error in the log files.

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table LOAD_TABLE
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at line 40

Connor McDonald
May 06, 2019 - 4:37 pm UTC

You can't run a truncate (sqlloader or otherwise) on a table that has an active transaction on it. You need to make sure you have exclusive access to that table to run a truncate.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.