A customer migrated an ETL application to a new Linux server and found that a SQL Loader job was now failing with a return code of 137.

I couldn’t find anything useful on the internet, documentation or Oracle support site about this sqlldr exit code, and there was nothing in the sqlldr logs.

I ran the job myself and noticed that the free memory on the server disappeared very quickly and remained low until the sqlldr process died.  (I didn’t have root access to check OS message logs).

The table being loaded had more than fifty VARCHAR2(4000) columns and direct path method was used.  Using the default SQLLDR parameter values, the direct load buffer needed over 1GB!
Setting a lower value for COLUMNARRAYROWS in the control file for this table reduced the memory requirement for loading this table, and allowed the job to complete successfully.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s