Getting OOM error while fetching 200,000 records from Oracle DB in Java

Question

My java app fetches about 200,000 records in its result set.

While trying to fetch the data from Oracle DB, the server throws java.lang.OutOfMemoryError: Java heap space

One way to solve this, IMO, is to fetch the records from the DB in smaller chunks (say 100,000 records in each fetch or even smaller count). How can I do this (meaning what API method to use)?

Kindly suggest how to do this or if you think there's a better way to overcome this memory space problem, do suggest. I do not want to use JVM params like -Xmx because I read that that's not a good way to handle OutOfMemory errors.

setFetchSize method should help. A smarter WHERE clause and processing strategy might be good, too. Where'd you read that? It's only bad if you don't understand root cause and postpone the inevitable. — duffymo
– duffymo, Commented Nov 23, 2013 at 15:48
lakh means 100,000. For the benefit of the non-Indian readers here though it should probably be written "100,000" :) — Sean Owen
– Sean Owen, Commented Jan 18, 2014 at 11:05

marc_s · Accepted Answer · 2014-01-18 10:17:38Z

1

If you are using Oracle DB you may set AND ROWNUM < XXX to your SQL query. This will cause that only XXX-1 rows will be fetched in query.

Other way is to call statement.setFetchSize(xxx) method before executing statement.

Setting larger JVM memory pool is poor idea, because in future there may be larger data set which will cause OOM.

edited Jan 18, 2014 at 10:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 23, 2013 at 15:53

MGorgon

2,60726 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting OOM error while fetching 200,000 records from Oracle DB in Java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related