Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Sql*Loader, Bequeath and ulimit

Re: Sql*Loader, Bequeath and ulimit

From: Laurenz Albe <>
Date: 03 Dec 2007 08:36:00 GMT
Message-ID: <>

ErikYkema <> wrote:
> 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_at_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?

I can confirm everything except for the operator's observations.

So when using sqlldr via BEQ protocol, the ulimits of the calling user should apply.

I would double check your operator's assumption; try and set ulimit -f to the desired value manually, check it with ulimit -a, then start the sqlldr process from the same shell and see what happens.

Laurenz Albe

PS: This is the C program I used to check my claim:

#include <stdio.h>
#include <errno.h>
#include <sys/time.h>
#include <sys/resource.h>

int main(int argc, char **argv) {
        struct rlimit64 rl;

        if (-1 == getrlimit64(RLIMIT_FSIZE, &rl)) {
                return 1;

        printf("Current file size limit: %lld bytes\n", rl.rlim_cur);
        return 0;

} Received on Mon Dec 03 2007 - 02:36:00 CST

Original text of this message