老是报如下异常
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into
bill (BILL_ID,PAYMENT_ID, BILLING_CYCLE_ID,OPERATORID, ACCOUNTID,PRODUCTID, BILL_AMOUNT,BLANCE, CREATED_DATE, STATE,acct_item
_id) select seq_bill.nextval, p.payment_id,'2008080101', 'systemAuto',0, s.productid,a.charge, a.charge,sysdate, 2,a.acct_item
_id from payment p, subscriber s, acct_item a where p.cust_id = s.cust_id and a.serv_id = s.subid and p.operation_type = 1 and
p.begin_date-1 <=a.acct_begin_date and p.end_date+1 >= a.acct_end_date and a.state='20C' and a.billing_cycle_id=?]; SQL state
[72000]; error code [1555]; ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small
; nested exception is java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too
small
Caused by:
java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:744)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:537)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:738)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:796)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:804)
at com.wafersystems.coss.service.impl.DealBillService.insertAllBill(DealBillService.java:187)
at com.wafersystems.coss.service.impl.DealBillService.dealWithAllAcctItem(DealBillService.java:148)
at com.wafersystems.coss.service.impl.DealBillService.run(DealBillService.java:103)
有时间是"_SYSSMU5$"和"_SYSSMU3$"
我采用如下方法
ALTER TABLESPACE undotbs ADD DATAFILE '/u01/oradata/undotbs2.dbf' SIZE 50M AUTOEXTEND ON;
和
alter database datafile 11 resize 2048m;
但是还是报回滚段太小。
各位帮我解决一下吧!!!!
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into
bill (BILL_ID,PAYMENT_ID, BILLING_CYCLE_ID,OPERATORID, ACCOUNTID,PRODUCTID, BILL_AMOUNT,BLANCE, CREATED_DATE, STATE,acct_item
_id) select seq_bill.nextval, p.payment_id,'2008080101', 'systemAuto',0, s.productid,a.charge, a.charge,sysdate, 2,a.acct_item
_id from payment p, subscriber s, acct_item a where p.cust_id = s.cust_id and a.serv_id = s.subid and p.operation_type = 1 and
p.begin_date-1 <=a.acct_begin_date and p.end_date+1 >= a.acct_end_date and a.state='20C' and a.billing_cycle_id=?]; SQL state
[72000]; error code [1555]; ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small
; nested exception is java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too
small
Caused by:
java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:744)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:537)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:738)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:796)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:804)
at com.wafersystems.coss.service.impl.DealBillService.insertAllBill(DealBillService.java:187)
at com.wafersystems.coss.service.impl.DealBillService.dealWithAllAcctItem(DealBillService.java:148)
at com.wafersystems.coss.service.impl.DealBillService.run(DealBillService.java:103)
有时间是"_SYSSMU5$"和"_SYSSMU3$"
我采用如下方法
ALTER TABLESPACE undotbs ADD DATAFILE '/u01/oradata/undotbs2.dbf' SIZE 50M AUTOEXTEND ON;
和
alter database datafile 11 resize 2048m;
但是还是报回滚段太小。
各位帮我解决一下吧!!!!
如果有,先recreate rollback segment以保持所有的回滚段大小一致,并且size不要太小。若要加大回滚段,可以用以下两种方法,最好不要把回滚表空间设成自动扩展
第一种是重建回滚段,修改minextents为一个较大的值,一个一个的offline,drop,create。初始化的大小为minextents*extent size
第二种是增加回滚段的个数
8i在有些情况下oracle会很喜欢用第一个回滚段,不知道为何,可以考虑将第一个回滚段扩大一点(如果错误信息里面是第一个回滚段的话)
RS.SHRINKS,RS.EXTENDS
FROM SYS.V_$ROLLNAME RN,SYS.V_$ROLLSTAT RS
WHERE RN.USN=RS.USN;结果贴出来我看看
(BILL_ID,
PAYMENT_ID,
BILLING_CYCLE_ID,
OPERATORID,
ACCOUNTID,
PRODUCTID,
BILL_AMOUNT,
BLANCE,
CREATED_DATE,
STATE,
ACCT_ITEM _ID)
SELECT SEQ_BILL.NEXTVAL,
P.PAYMENT_ID,
'2008080101',
'systemAuto',
0,
S.PRODUCTID,
A.CHARGE,
A.CHARGE,
SYSDATE,
2,
A.ACCT_ITEM _ID
FROM PAYMENT P, SUBSCRIBER S, ACCT_ITEM A
WHERE P.CUST_ID = S.CUST_ID
AND A.SERV_ID = S.SUBID
AND P.OPERATION_TYPE = 1
AND P.BEGIN_DATE - 1 <= A.ACCT_BEGIN_DATE
AND P.END_DATE + 1 >= A.ACCT_END_DATE
AND A.STATE = '20C'
AND A.BILLING_CYCLE_ID = ?;主要看你上面的select产生了多少数据,如果是十分庞大的话,增加会滚表空间也不行的,只能把把select的数据分成更小的集合来分布insert,分别commit来解决。
如果不是很大,增加会滚表空间应该就能解决。估计是你select的语句执行的效率很差导致的。
贴下面语句的执行计划把:
SELECT SEQ_BILL.NEXTVAL,
P.PAYMENT_ID,
'2008080101',
'systemAuto',
0,
S.PRODUCTID,
A.CHARGE,
A.CHARGE,
SYSDATE,
2,
A.ACCT_ITEM _ID
FROM PAYMENT P, SUBSCRIBER S, ACCT_ITEM A
WHERE P.CUST_ID = S.CUST_ID
AND A.SERV_ID = S.SUBID
AND P.OPERATION_TYPE = 1
AND P.BEGIN_DATE - 1 <= A.ACCT_BEGIN_DATE
AND P.END_DATE + 1 >= A.ACCT_END_DATE
AND A.STATE = '20C'
-- AND A.BILLING_CYCLE_ID = ?
;
I think what you need to do first is checking you sql.
Are you sure the program executes properly and commit every transaction in your application?
然后删除原来的回滚段
或者给回滚段添加数据文件