ORA-01562 ORA-01628
来源:互联网 发布:淘宝即将上架抢购攻略 编辑:程序博客网 时间:2024/06/13 17:16
一个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 segmentRB11 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%';
- ORA-01562 ORA-01628
- ORA-01562 和 ORA-01628 问题解决!
- ORA
- ora
- ORA-01034 ORA-27101
- ORA-10567,ORA-00313
- ora-01128,ora-00275
- ORA-03113,ORA-07445
- ORA-32017 ORA-16179
- ORA-12012,ORA-12005
- ora-01034 ,ora-27101
- ora-603 ora-27504
- ora-01113 ora-01110
- ORA-24324 & ORA-01041
- tnsnames.ora & listener.ora
- ORA-09817&ORA-01031
- ORA-00604 & ORA-25153
- ORA-38029 & ORA-20005
- 百度钱包的活动新出炉
- Android学习:UI线程规则+经典异常
- ubuntu 14.04搭建android编译环境
- 【spring框架】Spring简单类型注入
- iOS应用内购常见问题及注意事项
- ORA-01562 ORA-01628
- 类库、框架、平台的区别
- OpenSessionInViewFilter 的配置及替代方案
- Spark on Mesos: 粗粒度与细粒度实现分析
- Java Web笔记:XML解析的四种方法
- 从一个URL下载原始数据,基于byte字节
- 【spring框架】bean的生命范围(scope)
- 协议与委托
- Android 图片拖动