Skip to Main Content
  • Questions

  • Error while inserting data through SQL Loader

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sarat.

Asked: September 20, 2017 - 10:13 am UTC

Last updated: September 25, 2017 - 12:58 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi All,

We are using SQL loader in our .Net application to insert data into oracle Data base. We are inserting through bulk insert process.

We have another Stored procedure in Oracle which deletes data from the same table where insertion happens. But the data sets where deletion and insertion happen are completely different (Deletion happens on data which is Older than 2 days). Usually insertion happens in the morning and deletion happens in the evening. Till here we don't have any issues.

However when we run both Insertion and Deletion at the same time, deletion is happening successfully but insertion is failing. When we look at the SQL loader logs we get the error like
"SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired".

We are fine if the insertion process waits until the Deletion is completed, but it should not fail.

Can you please let us know how can we prevent the failure of the insertion.

and Connor said...

You didn't provide your control file or sqlldr command line, but most likely you are doing something that needs to lock the entire table, eg

- a TRUNCATE in the control file
- a direct path load

Those operations need an exclusive lock on the table, so if there are outstanding transactions you'll get that error.

You could look at using the "ddl_lock_timeout" parameter to get those style operations to wait for (say) 5 seconds in case a lock is outstanding, eg

-- session 1 (no commit)
SQL> delete from t where rownum = 1;

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

SQL> alter session set ddl_lock_timeout = 10;

Session altered.

SQL> truncate table t;
[waiting waiting.... somewhere in here we commit in session 1]

Table truncated.


Rating

  (2 ratings)

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

Comments

Sarat Chandra, September 25, 2017 - 9:49 am UTC

Thank you Connor for the reply.

Could you please let me know if we can pass the parameter 'ddl_lock_timeout'
through SQL Loader?

Connor McDonald
September 25, 2017 - 12:58 pm UTC

You'd need to either set it at database level, or use a login trigger to set it as session level when sqlldr starts, eg something like the below

create or replace
trigger TRACE_ALL_LOGINS
after logon on my_user.schema
disable
declare
  l_program varchar2(200);
begin
  select program
  into   l_program
  from   v$session
  where  sid = sys_context('USERENV','SID');
  
  if l_program like '%sqlldr%' then
    execute immediate 'alter session set tracefile_identifier = SQLLDR';
    execute immediate 'alter session set events = ''10046 trace name context forever, level 12''';
  end if;
end;


Another potential cause

Donald Stump, October 09, 2017 - 8:53 pm UTC

I got the same 3 line error message when I ran a python program that uses SQL Loader to load rows into an Oracle table. Here again are the error messages I got:
"SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired".

Turns out that I needed to do a commit of the database that contained the table I was loading before I could do another load. Once I did that, everything worked fine.
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.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.