rebuild online索引遇到ora-1450

测试一下
SQL> create table justin(name varchar2(4000));
 
Table created
 
SQL> create index idx_justin_name on justin(name);
 
Index created
 
SQL> alter index idx_justin_name rebuild online;
 
alter index idx_justin_name rebuild online
 
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
 
SQL> alter index idx_justin_name rebuild;
 
Index altered
去除online即可重建成功；
系统默认块为8k，现在创建一个16k的表空间；
SQL> show parameter db_block

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_block_buffers                     integer                          0
db_block_checking                    string                           FALSE
db_block_checksum                    string                           TRUE
db_block_size                        integer                          8192
SQL> show parameter db_16k_cache_size;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_16k_cache_size                    big integer                      0
SQL> alter system set db_16k_cache_size=1m;

System altered.

SQL> create tablespace justin datafile '/data/oracle/oradata/justin/justin.dbf' size 10m blocksize 16k;

Tablespace created.

重新rebuild online一下
SQL> alter index idx_justin_name rebuild online tablespace justin;
 
alter index idx_justin_name rebuild online tablespace justin
 
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3800) exceeded
依旧不行，但是错误信息的max key length从3215上升到3800；
SQL> drop tablespace justin; 

Tablespace dropped.

SQL> alter system set db_32k_cache_size=32k;

System altered.

SQL> create tablespace justin datafile '/data/oracle/oradata/justin/justin.dbf' size 10m blocksize 32k;

Tablespace created.

SQL> alter index idx_justin_name rebuild online tablespace justin;
 
alter index idx_justin_name rebuild online tablespace justin
 
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3800) exceeded
使用块大小为32k和16k的表空间，均是报告3800为上限，依据提示将索引列的长度调小为3780，这次在32k表空间里可以创建成功，但是在其他8k表空间依旧不行
SQL> alter table justin modify name varchar2(3780);
 
Table altered
 
SQL>  alter index idx_justin_name rebuild online tablespace justin;
 
Index altered
 
SQL>  alter index idx_justin_name rebuild online;
 
Index altered
 
SQL>  alter index idx_justin_name rebuild online tablespace justin;
 
alter index idx_justin_name rebuild online tablespace purchase
 
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

查看metalink ID 236329.1，
Rebuild the index without ONLINE clause. There is no way to rebuild this index
ONLINE without the change of the initialization parameter db_block_size.
OR
Rebuild the database with greater value of the initialization parameter
db_block_size according to Note:136158.1:
ORA-01450 and Maximum Key Length - How it is Calculated.

以后遇到此类错误，
要么去掉online选项，要么创建blocksize更大的表空间；但是后者并不能保证一定可以rebulid online通过

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-705231/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-705231/