Home » Other » Client Tools » SQL*Loader-700: Out of memory while performing essential allocations (Oracle 10g, AIX)
SQL*Loader-700: Out of memory while performing essential allocations [message #344763] Mon, 01 September 2008 04:00 Go to next message
mounir
Messages: 10
Registered: August 2008
Location: Paris
Junior Member
Hello all,

I have a problem when trying to run my control file using the SQL Loader. My control file is quite long and its size is about 175 kB. When I issue this command:

 sqlldr test@MYDB/test control=myControl.ctl


I get the following error:
SQL*Loader: Release 10.2.0.3.0 - Production on Mon Sep 1 10:58:16 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-700: Out of memory while performing essential allocations [7]



By looking for the origin of the problem on the internet, I read that the solution was to increase the memory size allowed to SQL Loader. Is there any way to do that ?
Re: SQL*Loader-700: Out of memory while performing essential allocations [message #344813 is a reply to message #344763] Mon, 01 September 2008 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL*Loader-00700: Out of memory while performing essential allocations [number]
Cause: SQL*Loader could not allocate the memory it needs.
Action: Check the operating system and process memory. Increase memory available to SQL*Loader, if possible. If using direct path, try lowering the columnarrayrows option (see log file for current default value).

Regards
Michel
Re: SQL*Loader-700: Out of memory while performing essential allocations [message #344838 is a reply to message #344813] Mon, 01 September 2008 06:25 Go to previous messageGo to next message
mounir
Messages: 10
Registered: August 2008
Location: Paris
Junior Member
Quote:

Increase memory available to SQL*Loader, if possible.



I already looked for the error description, and my question was how to increase memory available ? Is there any option in sqlldr to increase it ? Is there any structure (like a table) in Oracle that I could extend ?

Re: SQL*Loader-700: Out of memory while performing essential allocations [message #344843 is a reply to message #344838] Mon, 01 September 2008 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check ulimit. It is at OS level.

Regards
Michel
Re: SQL*Loader-700: Out of memory while performing essential allocations [message #344904 is a reply to message #344763] Mon, 01 September 2008 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My control file is quite long and its size is about 175 kB
I wonder how/why it could be that large.



>Doc ID: Note:149016.1
The NOTE above specifically mentions this error, but this Note is for Release 9.0.1.0

Are you attempting a direct path load?

Are you attempting to load rows in a column array?

Are you attempting to load LOB(s)?

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_managing.htm#sthref104

When using SQL*Loader direct-path load, loading LOBs can take up substantial memory. If the message "SQL*Loader 700 (out of memory)" appears when loading LOBs, then internal code is probably batching up more rows in each load call than can be supported by your operating system and process memory. A work-around is to use the ROWS option to read a smaller number of rows in each data save.

[Updated on: Mon, 01 September 2008 14:19] by Moderator

Report message to a moderator

Re: SQL*Loader-700: Out of memory while performing essential allocations [message #345096 is a reply to message #344904] Tue, 02 September 2008 05:23 Go to previous message
mounir
Messages: 10
Registered: August 2008
Location: Paris
Junior Member
Hi,

Thanks for the replies.


Quote:

I wonder how/why it could be that large



I must deal with dozens of tables, each one containing at least 40 columns. That's related to the business and I can't really change it.

Quote:

Are you attempting a direct path load?



Yes, and here is the header of my control file:

OPTIONS(rows = 500000, DIRECT=TRUE)
unrecoverable LOAD DATA
INFILE 'input_data.txt'
TRUNCATE

INTO
...


Quote:

Are you attempting to load rows in a column array?



Not in a direct way, but I guess that when you use direct=true column arrays are automatically used.

Quote:

Are you attempting to load LOB(s)?



No, I don't use LOB Files.
Previous Topic: Column headings in sql plus
Next Topic: TOAD Automatically Closing Problem
Goto Forum:
  


Current Time: Fri Mar 29 03:02:08 CDT 2024