OracleBulkCopy Memory Leak(OutOfMemory Exception)

bulkinsert c# memory-leaks oracle sqlbulkcopy

Question

Below is the code I used to bulkcopy data from a temp table dataTable into a destTable in Oracle Database. The dataTable has about 2 million records.

using (OracleBulkCopy bulkCopy = new OracleBulkCopy(VMSDATAConnectionString))
            {
                try
                {
                    foreach (OracleBulkCopyColumnMapping columnMapping in columnMappings)
                        bulkCopy.ColumnMappings.Add(columnMapping);

                    bulkCopy.DestinationTableName = destTableName;
                    //bulkCopy.BatchSize = dataTable.Rows.Count;
                    //bulkCopy.BulkCopyTimeout = 100;                   
                    int defaultSize = 5000;
                    int.TryParse(ConfigurationManager.AppSettings["OracleBulkCopyBatchSize"], out defaultSize);
                    bulkCopy.BatchSize = defaultSize;
                    int timeOut = 100;
                    int.TryParse(ConfigurationManager.AppSettings["OracleBulkCopyTimeout"], out timeOut);
                    bulkCopy.BulkCopyTimeout = timeOut;
                    Console.WriteLine("Bulk insert from {0} to {1} started at: {2}\r\nBatchSize : {3}, BulkCopyTimeout : {4} ", dataTable.TableName, destTableName, DateTime.Now.ToString("HH:mm:ss"), bulkCopy.BatchSize.ToString(), bulkCopy.BulkCopyTimeout.ToString());
                    bulkCopy.WriteToServer(dataTable);
                    Console.WriteLine("Bulk insert from {0} to {1} finished at: {2}", dataTable.TableName, destTableName, DateTime.Now.ToString("HH:mm:ss"));
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error happened during bulk copy from {0} to {1}\r\nBatchSize : {2}, BulkCopyTimeout : {3}\r\n Error message {4}", dataTable.TableName, destTableName, bulkCopy.BatchSize.ToString(), bulkCopy.BulkCopyTimeout.ToString(), ex.ToString());
                    bulkCopy.Close();
                    bulkCopy.Dispose();
                }
            }

But it throws following exception: enter image description here

The server running this data loading process definitely has enough memory, it looks like the database server (linux) does not have enough memory. Below is the database server memory screen shot: enter image description here

Can anyone help with this issue? Thanks.

Accepted Answer

Found the root cause, the exe is running in 32 bit and it has a 1.5G memory limit. Need to change the target platform and replace Oracle.DataAccess.dll to 64 bit version.

Also there is an alternative solution: load data in batch so it will not exceed 1.5 G memory limit.

Update:

"MEMORY LEAK USING ORACLEBULKCOPY": the oracle bulk copy has some bug that causes memory leak, it happens when the BatchSize is less than datatable size. Need to modify the BatchSize or update ODAC to higher version.

Reference: https://community.oracle.com/message/4593452#4593452




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why