ORA-01562 ORA-01628

一个oracle8i的测试库上，update数据时候提示错误，5w行左右！

大概跟我的库的init.ora配置有关，11g是spfile。前面两个文件都在database文件夹下面，和PWDorcl.ora在同一个文件夹。

---------------------------
发生'错误','错误'误代码为1562,ORA-01562: failed to extend rollback segment number 16
ORA-01628: max # extents (300) reached for rollback segment RB11
No changes made to database.

UPDATE "HNT_INSYZWD_DT_YBML" SET "SYBMC" = :1, "SYBJX" = :2, "SYBDW" = :3, "SYBCS" = :4, "SYPLC" = :5, "SSFDJ" = :6, "SXZFW" = :7, "SQUN" = :8, "NXFBL" = :9, "NXMDJ" = :10, "SCPDM" = :11, "SYBGG" = :12, "SZHBL" = :13, "SOTC" = :14, "SPZWH" = :15, "NZBJG" = :16, "NZBLJ" = :17 WHERE "SYBFL" = :18 AND "SYBDM" = :19 AND "SYBMC" = :20 AND "SYBJX" = :21 AND "SYBDW" = :22 AND "SYBCD" IS NULL AND "SYBCS" IS NULL AND "STJTZ" IS NULL AND "SYPLC" = :23 AND "SSFDJ" = :24 AND "SMLLB" = :25 AND "SXZFW" IS NULL AND "SQUN" = :26 AND "NXFBL" = :27 AND "SYXBZ" IS NULL AND "NXMDJ" = :28 AND "SCPDM" = :29 AND "SYBGG" IS NULL AND "SZHBL" IS NULL AND "SOTC" IS NULL AND "SPZWH" IS NULL AND "NZBJG" = :30 AND "NZBLJ" = :31 AND "SXMNH" IS NULL AND "SCWXM" IS NULL ,'错误'行43547
---------------------------
确定   

为了不down库解决，具体如下：

提示这两个错误说明回滚段空间不足了，原因是extent到最大maxextents了

上面看到的红色字体就是报错的回滚段

直接

sqlplus "/ as sysdba"

alter rollback segment RB11 storage (maxextents unlimited);

问题解决，如果还提示其他的回滚段，简单粗暴的方法就是批量生成SQL语句。

oracle 8i下面是这样的：

select 'alter rollback segment '||SEGMENT_NAME||' storage (maxextents unlimited);' from dba_segments t where  t.segment_name like 'RB%';

10g，11g大概是（8i下遇到解决了，10g,11g目前没遇到过，应该是没问题的）:

查看rollback segment

select SEGMENT_NAME,TABLESPACE_NAME,MAX_EXTENTS from dba_segments t where t.tablespace_name like 'UNDOTBS%';

生成语句

select 'alter rollback segment '||SEGMENT_NAME||' storage (maxextents unlimited);' from dba_segments t where t.tablespace_name like 'UNDOTBS%';