oracle 表在线重建,大表在线重建索引的考虑和碰到的限制问题-ORA-1450

本帖最后由 oudar 于 2016-7-10 10:26 编辑

在线重建索引alter index rebuild online 报 ORA-1450

平台：

OS:SunOS **** 11.2 sun4v sparc sun4v

DB:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Product

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Solaris:Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production复制代码背景：A表空间数据近年增长很快，数据文件个数已接近上限，不能再增加了，故新建一个表空间B，将其中的大索引迁到B中，由于系统为要求24小时可用的OLTP系统，版本为11g故选择使用rebuild online的方式重建索引到新的表空间B中。

另在增删改较多的系统中，也建议经常分析索引的碎片情况，达到一定比例如30%后建议安排重建以维持索引的高效性。

步骤1：

由于最大的索引达500G，但系统默认的TEMP表空间只有100G，这是远不够的，直接重建一是会失败，二是会影响其它正常SQL的排序资源需求，故新建一个500G的TEMP2表空间，将执行重建动作的DBMGR用户的默认表空间设置到TEMP2，这样即可保证重建时不会出现无法扩展TEMP段，也不会影响正常业务。

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/****/****/data20/oradata/****/temp2001' SIZE 2G AUTOEXTEND OFF;

ALTER TABLESPACE temp2 ADD TEMPFILE '/****/****/data20/oradata/****/temp2002' SIZE 2G AUTOEXTEND OFF;

......

ALTER DATABASE DATAFILE '/****/****/data20/oradata/****/temp2001' RESIZE 30G;

......

ALTER USER dbmgr TEMPORARY TABLESPACE temp2;复制代码

步骤2：

选择业务低峰窗口并行在线重建索引(10g/11g后新建和重建索引会自动收集索引统计信息，也可以在重建前后使用dbms_stats包备份的恢复统计信息)。

alter index **** rebuild online tablespace b parallel 8;

alter index **** parallel 1;

......复制代码

碰到的问题:

其中一个索引在重建时返回错误：

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded复制代码

检查该索引列得到长为VARCHAR2(4000)。原因：

以前对这个1450没什么概念，遂上metalink搜一把，便找到了原因了。

个人理解 ：在线重建索引的过程中，Oracle会使用一个临时的IOT表来存放该期间数据变化，而普通索引为二叉树结构，一个非叶子块至少要能存放一个左小值和一个右大值，另除去块头和pctfree等因数，能存放的最大key长度是有限制的。

请参考文档 136158.1

目前来说呢，这个不算是BUG，而是设计上的限制，并且一直到最新的12C都是有这个限制的，重点是我们要反思一个问题，为什么要在这么长的列上建索引呢？设计上是否合理？

同时该限制导致的报错引起的BUG请参考文档18854.1WORKAROUND:

1、参考上面的文档使用更大的block_size的表空间来在线重建该索引；

2、选择系统负载低的窗口，使用高并行度快速重建(非online)。