How to overcome OutOfMemoryError during huge file recording

Question

I am writing a complete database extraction program in java. The database is Oracle, and it is huge. Some tables have ~ 260 million records. The program must create a single file in the table in a specific format, so use Oracle datapump, etc. Not an option. In addition, some company security policies do not allow writing a PL / SQL procedure to create files on the database server for this requirement. I need to go with Java and JDBC.

The problem I am facing is that since the files for some tables are huge (~ 30 GB), I run out of memory almost every time even with 20 GB of Java heap. During file creation, when the file size exceeds the heap size, even with one of the most aggressive GC policies, the process seems to freeze. For example, if the file size is> 20 GB and the heap size is 20 GB, then when the heap load reaches the maximum heap size, its speed will slow down by writing 2 MB per minute or so, and full extraction will be required at this speed.

I am looking for a way to overcome this problem. Any help would be greatly appreciated.

Here are some system configuration details that I have: Java - JDK1.6.0_14

System configuration - RH Enterprise Linux (2.6.18), running on 4 X Intel Xeon E7450 (6 cores), 2.39GH

RAM - 32 GB

Oracle 11g Database

The wirting file part of the code goes below:

private void runQuery(Connection conn, String query, String filePath, String fileName) throws SQLException, Exception { PreparedStatement stmt = null; ResultSet rs = null; try { stmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(maxRecBeforWrite); rs = stmt.executeQuery(); // Write query result to file writeDataToFile(rs, filePath + "/" + fileName, getRecordCount( query, conn)); } catch (SQLException sqle) { sqle.printStackTrace(); } finally { try { rs.close(); stmt.close(); } catch (SQLException ex) { throw ex; } } } private void writeDataToFile(ResultSet rs, String tempFile, String cnt) throws SQLException, Exception { FileOutputStream fileOut = null; int maxLength = 0; try { fileOut = new FileOutputStream(tempFile, true); FileChannel fcOut = fileOut.getChannel(); List<TableMetaData> metaList = getMetaData(rs); maxLength = getMaxRecordLength(metaList); // Write Header writeHeaderRec(fileOut, maxLength); while (rs.next()) { // Now iterate on metaList and fetch all the column values. writeData(rs, metaList, fcOut); } // Write trailer writeTrailerRec(fileOut, cnt, maxLength); } catch (FileNotFoundException fnfe) { fnfe.printStackTrace(); } catch (IOException ioe) { ioe.printStackTrace(); } finally { try { fileOut.close(); } catch (IOException ioe) { fileOut = null; throw new Exception(ioe.getMessage()); } } } private void writeData(ResultSet rs, List<TableMetaData> metaList, FileChannel fcOut) throws SQLException, IOException { StringBuilder rec = new StringBuilder(); String lf = "\n"; for (TableMetaData tabMeta : metaList) { rec.append(getFormattedString(rs, tabMeta)); } rec.append(lf); ByteBuffer byteBuf = ByteBuffer.wrap(rec.toString() .getBytes("US-ASCII")); fcOut.write(byteBuf); } private String getFormattedString(ResultSet rs, TableMetaData tabMeta) throws SQLException, IOException { String colValue = null; // check if it is a CLOB column if (tabMeta.isCLOB()) { // Column is a CLOB, so fetch it and retrieve first clobLimit chars. colValue = String.format("%-" + tabMeta.getColumnSize() + "s", getCLOBString(rs, tabMeta)); } else { colValue = String.format("%-" + tabMeta.getColumnSize() + "s", rs .getString(tabMeta.getColumnName())); } return colValue; 

}

Answer 1

This is probably due to the fact that you call prepareStatement , see this question for a similar task. You don't need to scroll, and the ResultSet will be read-only, so just call

 stmt = conn.prepareStatement(query); 

Answer 2

Edit : Map the database tables to the usig JPA class.
Now load the collection of objects from the database using Hibernate in a batch package of some acceptable size and serialize it in FILE.

Answer 3

Is your algorithm as follows: This involves direct matching between rows and rows of the database in the file:

 // open file for writing with buffered writer. // execute JDBC statement // iterate through result set // convert rs to file format // write to file // close file // close statement/rs/connection etc 

Try using the Spring JDBC template to simplify the JDBC part.

Answer 4

I believe this should be possible by default 32 MB java heap. Just load each line, save the data to a file stream, execute a flash and close it.

Answer 5

What value are you using for maxRecBeforWrite?

Perhaps querying the maximum record length breaks your setFetchSize, causing JDBC to scan the entire result for the record length? Perhaps you could delay the title record and note the maximum record size on the fly.