Purging Process halts after running 10 delete queries - Finally exiting with java.sql.BatchUpdateExc

[Jay B Shukla]

have a requirements to create a purging program using Java to purge record from database. Records will be purged from each tables before certain date (depending on defined in property file).

I have created program and decided all tables in to certain functions (NOT database function, but Java functions user defined) so that it can handles interdependent tables together. This program runs fine till 10 queries (first 4 functions) but halts at 5 the function of sequence defined.

In this case it takes very long time > 20 - 30 mins and finally aborted with following exception.

java.sql.BatchUpdateException: error occurred during batching: No more data to read from socket
        at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:444)
        at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:489)
        at oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:3669)
        at com.delta.fastfollower.database.OracleUtil.executeBatch(OracleUtil.java:78)
        at com.delta.fastfollower.admin.PurgeDatabaseOperations.purgeClsOfSvcHistTable(PurgeDatabaseOperations.java:133)
        at com.delta.fastfollower.admin.WaiverManagementPurgingMain.main(WaiverManagementPurgingMain.java:95)
2016-01-13 12:25:22 DEBUG PurgeDatabaseOperations:purgeClsOfSvcHistTable:135 - Failure Occured in purgeClsOfSvcHistTable() - return values : 1

Could anyone please suggest me where do i need to start digging into?

Regards,
Jay Shukla

[Jay B Shukla]

Just miss to add -- This is a standalone Java program. Oracle is 11g.

[Roel De Nijs]

Jay B Shukla wrote:Could anyone please suggest me where do i need to start digging into?

From a quick google search it seems it's related to an error to the database server itself. So maybe check the oracle logs first to see if you can find the root cause.

[Dave Tolls]

How much is being purged?
If it's a lot then the connection could simply be timed out.

Have you tested the DELETE statements in the database directly?

[Martin Vashko]

I believe that connection which is doing some work shouldn't time out, but I might be wrong.

However, I've noticed this is a batch update exception. I've seen network errors to happen when using large batches, specifically with Oracle. It's natural to expect that the bigger the batch, the better - and faster, but unfortunately a batch which is too large (larger than, say, a hundred of records) isn't going to perform any better than moderately sized batches and can actually fail.

How large are your batches, Jay?