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 12:20:16 -0800 (PST)
Message-ID: <a0fc1df4-2b4b-4e8a-aa06-8f71ff321201@d4g2000prg.googlegroups.com>


On Nov 30, 8:59 pm, bdbafh <bdb..._at_gmail.com> wrote:
> On Nov 30, 2:53 pm, ErikYkema <erik.yk..._at_gmail.com> 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')
> > 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
>
> Try and see if a conventional load fails with the same error.
> I'm assuming that you're using direct load now.
> Yes, in direct load, the user's session is writing directly to the
> datafile (above the high water mark) rather than going through the
> buffer cache.
>
> I don't have an AIX box to test this out on, and I don't have any
> boxes < 10g R1.
>
> -bdbafh

Thanks 'bastard dba from hell' (that's what it means I assume :)) Unfortunately we found out that direct=false, else I would have better understood this. regards, erik Received on Fri Nov 30 2007 - 14:20:16 CST

Original text of this message

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