java - 贾斯珀报告错误 : ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old

Question

当我使用Jasper作为接口(interface)时，有时接口(interface)会调用jasper并生成报告，然后我们会得到如下异常:

================================================================================
org.springframework.jdbc.UncategorizedSQLException: 
    Hibernate operation: Could not execute JDBC batch update; 
    uncategorized SQLException for SQL [update JIRepositoryCache set data=?, version=?, version_date=?, item_reference=? where id=?]; 
SQL state [72000]; 
error code [1555]; ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

; nested exception is java.sql.BatchUpdateException: ORA-01555: snapshot too old: rollback segment number  with name "" too small


ORA-22924: snapshot too old



    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)

    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

    at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)

    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)

    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)

    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)

    at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1055)

    at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1048)

    at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryCache.getCachedItem(HibernateRepositoryCache.java:126)

    at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryCache.getCachedItem(HibernateRepositoryCache.java:99)

    at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryCache.cache(HibernateRepositoryCache.java:67)

    at sun.reflect.GeneratedMethodAccessor556.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

    at java.lang.reflect.Method.invoke(Method.java:597)

    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)

    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)

    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)

    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)

    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

    at com.jaspersoft.jasperserver.api.common.util.spring.ReattemptMethodInterceptor.invoke(ReattemptMethodInterceptor.java:51)

    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)

    at $Proxy16.cache(Unknown Source)

    at com.jaspersoft.jasperserver.multipleTenancy.PathTransformationRepositoryCache.cache(PathTransformationRepositoryCache.java:52)

    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.getCompiledReport(EngineServiceImpl.java:897)

    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.getJasperReport(EngineServiceImpl.java:659)

    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:369)

    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:872)

    at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:57)

    at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:319)

    at com.jaspersoft.ji.engine.ProEngineServiceImpl.execute(ProEngineServiceImpl.java:132)

    at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeReport(ReportExecutionJob.java:494)

    at com.jaspersoft.ji.report.options.engine.ReportOptionsExecutionJob.executeReport(ReportOptionsExecutionJob.java:103)

    at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeAndSendReport(ReportExecutionJob.java:422)

    at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.execute(ReportExecutionJob.java:231)

    at org.quartz.core.JobRunShell.run(JobRunShell.java:195)

    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)

Caused by: java.sql.BatchUpdateException: ORA-01555: snapshot too old: rollback segment number  with name "" too small

ORA-22924: snapshot too old



    at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)

    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10698)

    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)

    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)

    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)

    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)

    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)

    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)

    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:322)

    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)

    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028)

    at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)

    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:420)

    ... 32 more
================================================================================

数据库中没有出现ORA-015555和ORA-22924。我们将 undo_retention 参数增加到 8 小时，但仍然遇到问题。

有没有人遇到过这个问题并有解决办法？

Answer 1

可能您试图一次读取太多的 LOB。如果是这样 - 那么

1. 尝试重新定义要读取的具体 LOB(可能的原因是 select * from table_with_many_lobs。删除它即可解决您的问题。)
2. 尝试增加保留区域大小(请参阅 here )

3. 尝试调整用于保存每列保留数据的默认存储百分比。喜欢

   更改表 YOUR_LOB_TABLE 修改 LOB (LOB_COLUMN_NAME) (PCTVERSION 50)；

例如。希望这有帮助)