ErikYkema wrote:
> On Nov 30, 8:41 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> 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
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Fri Nov 30 2007 - 14:14:12 CST
