1.产生问题

SQL> drop table t purge;

Table dropped.

SQL> create table t as select rownum id ,dbms_random.string('x',16)  name1,dbms_random.string('x',16) name2  from dual connect by level<=10000;

Table created.

SQL> create index i_t_name1 on t(name1);

Index created.

SQL> alter index i_t_name1 rebuild  ;

Index altered.

SQL> alter index i_t_name1 rebuild online  ;

alter index i_t_name1 rebuild online

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded

--why?查看表的结构,这样定义的表name1,name2字段的长度是4000.

SQL> desc t;

Name   Null?    Type

------ -------- --------------

ID              NUMBER

NAME1            VARCHAR2(4000)

NAME2            VARCHAR2(4000)

--原因应该在这里.那为什么rebuild正常,rebuild online要报错呢.

--先来建立如下索引,建立如下索引:

SQL> create index i_t_name1 on t(name1,name2);

create index i_t_name1 on t(name1,name2)

                          *

ERROR at line 1:

ORA-01450: maximum key length (6398) exceeded

--oracle害怕建立的索引长度超过6398的限制,虽然现在不超,但是以后可能出现,oracle直接在建立的时候报错.

而建立name1字段索引,明显没有超过6398的限制,但是为什么rebuild online的时候会报错了.
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

2.10046跟踪:

create index i_t_id on t(id);

alter session set events '10046 trace name context forever, level 12';

alter index i_t_id rebuild online ;

alter session set events '10046 trace name context off';

--格式化tkprof

SQL ID: 87wdynhsuc1y9

Plan Hash: 1380846739

CREATE UNIQUE INDEX "SCOTT"."SYS_IOT_TOP_96976" on

  "SCOTT"."SYS_JOURNAL_96974"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE

  "USERS" STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE

  DEFAULT) NOPARALLEL

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0         41           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.00       0.00          0          0         41           0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84     (recursive depth: 2)

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  INDEX BUILD UNIQUE SYS_IOT_TOP_96976 (cr=0 pr=0 pw=0 time=0 us)(object id 0)

      0   SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=0)

      0    TABLE ACCESS FULL SYS_JOURNAL_96974 (cr=0 pr=0 pw=0 time=0 us)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  asynch descriptor resize                        2        0.00          0.00

SQL ID: fyppbt0tbp3z0

Plan Hash: 0

create table "SCOTT"."SYS_JOURNAL_96974" (C0 NUMBER,  opcode char(1), partno

  number,  rid rowid, primary key( C0 , rid )) organization index TABLESPACE

  "USERS"

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.01          0          0          3           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.00       0.01          0          0          3           0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84     (recursive depth: 1)

SQL ID: 3s384gtcpccvv

Plan Hash: 0

drop table "SCOTT"."SYS_JOURNAL_96974" purge

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.01       0.01          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.01       0.01          0          0          0           0

Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: SYS   (recursive depth: 1)

--可以发现rebuild online的过程中要建立一张SYS_JOURNAL_96974的IOT表.以后删除的过程.

SQL> select object_id,data_object_id,object_name from dba_objects where object_name='I_T_ID';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME

---------- -------------- -----------------

     96974          96975 I_T_ID

--数字正好与建立索引的object_id对应.

create table "SCOTT"."SYS_JOURNAL_96974" (C0 NUMBER,  opcode char(1), partno

  number,  rid rowid, primary key( C0 , rid )) organization index TABLESPACE

  "USERS"

--从建立的IOT表看,包含如下字段c0,opcode,partno,rid 字段.一定是IOT组织表的特性,限制了索引建立的长度.c0应该与表T的id相对应.

--执行如下命令:

create table t2 ( c0 varchar2(4000), primary key(c0)) organization index;

SQL> create table t2 ( c0 varchar2(4000), primary key(c0)) organization index;

create table t2 ( c0 varchar2(4000), primary key(c0)) organization index

*

ERROR at line 1:

ORA-01450: maximum key length (3215) exceeded

--再次确定问题就是rebuild online,需要建立这样的IOT表,而里面包含varhcar2(4000)字段,导致出现ORA-01450: maximum key length (3215) exceeded错误.

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