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透過