SQL*Loader-700: Out of memory while performing essential allocations [7] and unable To Load Using Direct Sqlldr Path Option SQL*Loader-700 are very common error in SQL Loader which we are trying to fix it.
For either of these cases, set the SQL Loader COLUMNARRAYROWS parameter to lower the number of rows in a column array. This will reduce the amount of memory SQL Loader attempts to allocate during a direct path load.
How to Fix SQL*Loader-700: errors
Steps to Resolve it:
- Patameter columnarrayrows needs to be set to a lower value.
- Ulimits are not set properly -modify ulimit for data to unlimited.
To implement the solution, please execute the following steps:
Set parameter columnarrayrows to a low value. Ex. :
C:\temp>sqlldr control=control.ctl data=data.dat userid=scott/tiger direct=y columnarrayrows=1000
Default: To see the default value for this parameter, invoke SQL*Loader without any parameters.
This parameter (columnarrayrows): Specifies the number of rows to allocate for direct path column arrays. The value for this parameter is not calculated by SQL*Loader. You must either specify it or accept the default.
This value results in improved performance for most cases. However, because that amount can be a larger number than would have been derived by the calculation method, it may negatively affect the load performance depending on the memory available.