Monday, January 31, 2011

JDBC & ORA-01555: snapshot too old: rollback segment number with name "" too small

I've encountered this Oracle error while trying to update CLOB column from Java app (using JDBC):

ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

All of the stuff I was able to google on the subject was concerning reading of the data and not writing it (e.g. exporting LOBs). From what I've learned, 'name ""' part means that the problem is related specifically to the LOB tablespace/undo segment.

I've tried to localize the problem and found out that the problem seems to be in the JDBC part.
I was copying CLOB values from one DB to another like this:

Clob clob = rs.getClob(i + 1);
updateCLOBs.setClob(i + 1, clob);

Replacing it with this:
String clobStr = rs.getString(i + 1);
updateCLOBs.setString(i + 1, clobStr);

helped to resolve the problem.

I've tested the second approach of copying the CLOB with values of over 4000 chars length and it works.