老是报如下异常
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会很喜欢用第一个回滚段,不知道为何,可以考虑将第一个回滚段扩大一点(如果错误信息里面是第一个回滚段的话)
2 RS.SHRINKS,RS.EXTENDS
3 FROM SYS.V_$ROLLNAME RN,SYS.V_$ROLLSTAT RS
4 WHERE RN.USN=RS.USN;NAME RS.RSSIZE/1024 GETS WAITS (RS.WAITS/RS.GETS)*100 SHRINKS EXTENDS
------------------------------ -------------- ---------- ---------- ---------------------- ---------- ----------
SYSTEM 376 143 0 0 0 0
_SYSSMU1$ 440 6888 0 0 0 0
_SYSSMU2$ 504 9534 0 0 0 0
_SYSSMU3$ 504 9048 0 0 0 0
_SYSSMU4$ 504 6674 0 0 0 0
_SYSSMU5$ 504 9525 0 0 0 0
_SYSSMU6$ 504 9041 0 0 0 0
_SYSSMU7$ 2168 6889 0 0 0 0
_SYSSMU8$ 504 9523 0 0 0 0
_SYSSMU9$ 440 9033 0 0 0 0
_SYSSMU10$ 376 6669 0 0 0 011 rows selected你的结果贴出来我看看
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 = ?;主要看你上面的select产生了多少数据,如果是十分庞大的话,增加会滚表空间也不行的,只能把把select的数据分成更小的集合来分布insert,分别commit来解决。
如果不是很大,增加会滚表空间应该就能解决。
RS.RSSIZE / 1024,
RS.GETS,
RS.WAITS,
(RS.WAITS / RS.GETS) * 100,
RS.SHRINKS,
RS.EXTENDS
FROM SYS.V_$ROLLNAME RN, SYS.V_$ROLLSTAT RS
WHERE RN.USN = RS.USN 2 3 4 5 6 7 8 9
10 ;NAME RS.RSSIZE/1024 GETS WAITS
------------------------------ -------------- ---------- ----------
(RS.WAITS/RS.GETS)*100 SHRINKS EXTENDS
---------------------- ---------- ----------
SYSTEM 376 27652 0
0 0 0_SYSSMU1$ 12408 2254948 77
.003414713 223 534_SYSSMU2$ 16504 2986766 215
.007198421 540 1084
NAME RS.RSSIZE/1024 GETS WAITS
------------------------------ -------------- ---------- ----------
(RS.WAITS/RS.GETS)*100 SHRINKS EXTENDS
---------------------- ---------- ----------
_SYSSMU3$ 16504 3057054 319
.010434883 547 1047_SYSSMU4$ 14456 2260378 78
.00345075 241 671_SYSSMU5$ 17528 2970268 180
.006060059 523 976
NAME RS.RSSIZE/1024 GETS WAITS
------------------------------ -------------- ---------- ----------
(RS.WAITS/RS.GETS)*100 SHRINKS EXTENDS
---------------------- ---------- ----------
_SYSSMU6$ 17528 2991116 218
.00728825 551 1033_SYSSMU7$ 13432 2223395 69
.003103362 196 513_SYSSMU8$ 15480 2981396 166
.005567861 514 980
NAME RS.RSSIZE/1024 GETS WAITS
------------------------------ -------------- ---------- ----------
(RS.WAITS/RS.GETS)*100 SHRINKS EXTENDS
---------------------- ---------- ----------
_SYSSMU9$ 16504 2982363 177
.005934891 533 1015_SYSSMU10$ 12408 2241339 67
.002989285 227 581
供大家参考,伴我提出解决办法
select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11$ OFFLINE UNDOTBS2
_SYSSMU12$ OFFLINE UNDOTBS2
_SYSSMU13$ OFFLINE UNDOTBS2
_SYSSMU14$ OFFLINE UNDOTBS2
_SYSSMU15$ OFFLINE UNDOTBS2
_SYSSMU16$ OFFLINE UNDOTBS2
_SYSSMU17$ OFFLINE UNDOTBS2
_SYSSMU18$ OFFLINE UNDOTBS2
_SYSSMU19$ OFFLINE UNDOTBS2
_SYSSMU20$ OFFLINE UNDOTBS2已选择21行。
说明是有问题的。处理过程
1 create undo tablespace undotBS2 datafile 'D\:oracleoradataPS30710UNDOTBS2.DBF' size 100m;
alter system set undo_tablespace=undotBS2;drop tablespace undotbs1 including contents;(进行这部操作的时候会报下面的错):
ORA-01548: 已找到活动回退段'_SYSSMU1$',终止删除表空间2 修改文件init.ora.162007221035,如下:
undo_management=manual
undo_retention=10800
undo_tablespace=undotBS2
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
3 启动服务
startup pfile='D\:oracleadminPS30710pfileinit.ora.421200715439'
4 删除表空间
drop tablespace undotbs1 including contents;
create undo tablespace undotBS1 datafile 'D:oracleoradataPS30710UNDOTBS01.DBF' size 200m;
drop tablespace undotBS2 including contents;
5 修改init.ora.162007221035,如下:
undo_management=auto
undo_retention=10800
undo_tablespace=undotBS1
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
6 关闭服务,并且用下面的命令重新启动服务
startup pfile='D\:oracleadminPS30710pfileinit.ora.421200715439'
7 拷贝spfile文件,原先的spfile文件做好备份
create spfile='D\:oracleora92databaseSPFILEPS30710.ORA' FROM pfile='D:oracleadminPS30710pfileinit.ora.421200715439';
8 关闭服务器,重新启动服务器,即可。
select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ ONLINE UNDOTBS1
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1已选择11行。