Oracle rebuild index 报 ORA-01652 解决办法
来源:互联网 发布:三菱plc触摸屏编程视频 编辑:程序博客网 时间:2024/06/02 16:02
早上刚开QQ,群里的一朋友就说rebuild index 报ORA-01652错误。并且temporary tablespace 是足够大的。 rebuild 索引是6G,indextablespace 剩余空间是2G。让朋友把index tablespace 空间增加10G,在rebuild index成功。
之前整理过的一篇有关rebuildindex 的文章:
Oracle alter index rebuild 说明
http://blog.csdn.net/xujinyang/article/details/6823011
关于这个问题MOS 上有2篇文档进行了相关的说明:[ID 94178.1] 和 [ID 120360.1]。
执行如下命令时:
ALTER INDEX REBUILD or
ALTER TABLE MODIFY PARTITION REBUILD LOCALINDEXES
可能会遇到ORA-01652的错误, 该错误详细解释如下:
ORA-01652:unable to extend temp segment by %s in tablespace %s
Cause: Failed to allocate an extent for temp segmentin tablespace.
Action:Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to thetablespace indicated or create the object in other tablespace.
解决方法:
You will have to increase the amount of available free space in the index tablespaceeither by adding another datafile or enabling autoextend on an existingdatafile.
--增加索引表空间添加数据文件或者将数据文件改成autoextend。
解释:
Thetablespace in the error message is pointing to the index's tablespace insteadof the user's default temporary tablespace.
During an index rebuild, there are two types oftemporary segments involved.
First, there are the temporarysegments that are used to store partial sort data when the SORT_AREA_SIZE istoo small to process the complete sort set. These segments are built in theuser's default TEMPORARY tablespace.
Second, as the index is being rebuilt, it uses a segment which is defined as atemporary segment until the rebuild is complete. Once this segment is fully populated,the old index can be dropped and this temporary segment is redefined as apermanent segment with the index name.
--在索引rebuild 期间,会产生2种temporary segments。
第一种是用来排序的temporarysegments,当SORT_AREA_SIZE 的值太小时,该segments 会在用户默认的temporary tablespace 空间分配。
第二种是在用户索引空间的segments,在rebuild index时,会分配一个temporary segment 来保存索引的信息,当rebuild 结束之后,old index 被droped 掉,之前分配的temporary segments 会定义为permanent segment。
所以对索引进行rebuild 至少要提供1倍以上的空闲空间来存放temporary segment。 否则就会出现ORA-01652的错误。
The error you are seeing is probably due to there being insufficient room in theindex's tablespace to hold both the original index and the new version concurrently.The new version of the index, currently a temp segment, will be in thetablespace where the index is required.
As an index is being rebuilt, it uses a segment which is defined as a temporarysegment for the rebuild. Once this segment is fully populated, the allocationof the old index is set to temporary and the populated temporary segment isredefined as a permanent segment with the index name.
Now if the storage clause (next extent for example) for the existing index is setto a very high number (64MB or so) and you don't specify a storage clause withthe rebuild command Oracle will use the storage clause of the existing index toallocate the space for the temporary segments.
This will allocate a lot of (unneeded) space and so you will run into the ora-1652.
可以检查索引storageclause的配置,看这些值是否合适。
/* Formatted on 2011/8/3 12:27:36(QP5 v5.163.1008.3004) */
SELECT OWNER,
INDEX_NAME,
TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE
FROM dba_indexes;
- Oracle rebuild index 报 ORA-01652 解决办法
- Oracle rebuild index 报 ORA-01652 解决办法
- Oracle alter index rebuild 与 ORA-08104 说明
- rebuild oracle index
- oracle index rebuild online
- Oracle alter index rebuild 说明
- ORACLE中index的rebuild
- ORACLE中index的rebuild
- ORACLE中index的rebuild
- rebuild index online prompted ora-08106
- Oracle Lob类型存储浅析( alter index lob索引 rebuild tablespace XX报错问题)
- rebuild index
- index rebuild
- rebuild index
- Rebuild index online 引起的ORA-8104错误
- Oracle index rebuild online 与 rebuild 及 drop index 后重建
- oracle alter index rebuild online和alter index rebuild的区别
- Oracle报 ORA-00054资源正忙的解决办法
- 点击按钮时显示按钮被按下的效果
- 面试经验
- VC 没有可以用于当前位置源代码(.net同样适用)
- 万全慧眼!解读联想服务器第4代管理软件
- Oracle ORA_ROWSCN 伪列 说明
- Oracle rebuild index 报 ORA-01652 解决办法
- 网站优化--让你的网页飞起来
- Oracle alter index rebuild 说明
- Mysql互为主从问题--日志同步数据不同步
- 联想万全慧眼V专业版监控管理系统详解
- Vmware 虚拟机和主机的链接问题
- 正则表达式在EditPlus中的常见应用
- Struts2输入校验(四) の校验框架 ——非字段校验
- swing:为什么一个类里调用另一个类的panel不显示