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:
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:
Can anyone help with this issue? Thanks.