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 20:41:15 +0100
Message-ID: <fipp0r$sr5$>

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/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?
> 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!

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?!?

Frank van Bortel

Top-posting is one way to shut me up...
Received on Fri Nov 30 2007 - 13:41:15 CST

Original text of this message