Sql*Loader, Bequeath and ulimit

Giganews Newsgroups
Subject: Sql*Loader, Bequeath and ulimit
Posted by:  ErikYkema (erik.yke…@gmail.com)
Date: Fri, 30 Nov 2007

Hello,
We just experienced the following ulimit event in a production
Sql*Loader run, and I am looking for your help in understanding why it
is as we saw.

Oracle EE 817@AIX5L.
Ulimit for Oracle for fsize is unlimited, ulimit fsize for user that
calls sqlldr is 2 GB. We insert (not direct path) and connect using
beq (no @SID in connect string) and receive a message like:
ORA-01115: IO error reading block from file 1 (block # 5970)
ORA-01110: data file 1: '/db8/rstst/system.dbf'
ORA-27092: skgfofi: size of file exceeds file size limit of the
process

It turns out that this datafile is around 2GB in size.
I first thought that the instance had been started in a wrong way,
using wrong (2GB) ulimit. A bounce of the instance with correct
environment didn't solve it.

We were able to insert the rows using Sql*Plus inserts manually (and
to extend the dbf.)

We found some references on the internet confirming the problem and
the following suggested approaches:
- either make sure the user that calls sqlldr has a ulimit at least
equal to oracle
- and/or make sure not to connect sqlldr using beq

An operator assures us that he has set the ulimit fsize (hard and
soft) for the calling user to 4 GB, and still was not able to complete
the sqlldr run succesfully, on the same error.

Now our assumption for explaining this is:
When calling the sqlldr executable using beq, the oracle server side
process that writes (and reads) the data file does not have exactly
the same ulimit as the other instance oracle processes. It also seems
not to have the calling user's ulimit. (If the operator's observation
was correct.)

Why would/could this process be different? What is exactly the nature
of this process? Or is something else going on?
Would this case also hold for imp and other Oracle Utilities?

Regards,
Erik

Replies