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: Frank van Bortel <>
Date: Fri, 30 Nov 2007 21:14:12 +0100
Message-ID: <fipqum$hkd$>

ErikYkema wrote:

> On Nov 30, 8:41 pm, Frank van Bortel <>
> wrote:

>> ErikYkema wrote:
>>> 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_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/user.dbf' # quoted post changed by Erik
>>> 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
>> There are some flaws in your posting:
>> - your error has nothing to do with sqlldr, or whatever process,
>> but with a clumsy AIX sysadmin, that did not enable large files.
>> Come on, you're on 5L!
>> - you insert data into tables, located in the SYSTEM tablespace.
>> Don't - your instance will, sooner or later, come to a grinding halt.
>> - I may be wrong on this one, but back in the 8i days, imp and exp
>> executables were 32 bits, thereby restricting to file operations on
>> files no larger than 2GB. Not sure if the same applies to sqlldr.
>> But even if it did, that just means your input file can be no larger
>> than 2GB-1 - again, not connected to the error.
>> And... you're at least 3 MAJOR releases behind schedule: 9iR1,
>> 9iR2, 10GR1. 10GR2 is current, 11G is out, and you're still on
>> 8i?!?
>> --
>> Regards,
>> Frank van Bortel
>> Top-posting is one way to shut me up...
> Hi Frank,
> Thanks for your answer.
> I was inappropriate in my sample, it was in reality a user tablespace
> and didn't see that my copy-pasted sample was to system. Sorry for
> that. (Though I said '_like_ the error below')

Missed that - basically skimmed the posting; hard day at work trying to get !&*#@(% iAS 10.1.3 to behave on a 32GB HP-UX cluster.

> We have many datafiles for this instance that are much larger than 2
> GB. (So I think we do have 'large files' enabled.)

Erhm - you just may have a point here... ^-8

> Yes I am aware of the age of 8i - it is what is is. I expected the > rant.

Was I ranting? Merely explaining the state of business in oracle land.

> Your last thought triggers a question: is sqlldr modifying the
> datafile, I expected it to be a database instance process, connected
> to the session initiated by sqlldr.
> More thoughts are appreciated. The issue seems to be: non-oracle user
> ulimits applied when using beq, not when using Sql*Net.
> Regards, Erik Ykema

Well, sorta' like bdbafh... haven't seen AIX in a while

Frank van Bortel

Top-posting is one way to shut me up...
Received on Fri Nov 30 2007 - 14:14:12 CST

Original text of this message