Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: Sql*Loader, Bequeath and ulimit

From: ErikYkema <erik.ykema_at_gmail.com>
Date: Fri, 30 Nov 2007 11:53:08 -0800 (PST)
Message-ID: <a007462f-f109-434a-a80c-ffbe6d2d5e16@j44g2000hsj.googlegroups.com>


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') We have many datafiles for this instance that are much larger than 2 GB. (So I think we do have 'large files' enabled.)

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

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 Received on Fri Nov 30 2007 - 13:53:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US