1.产生问题
--why?查看表的结构,这样定义的表name1,name2字段的长度是4000.
--原因应该在这里.那为什么rebuild正常,rebuild online要报错呢.
--先来建立如下索引,建立如下索引:
--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跟踪:
--可以发现rebuild online的过程中要建立一张SYS_JOURNAL_96974的IOT表.以后删除的过程.
--数字正好与建立索引的object_id对应.
--从建立的IOT表看,包含如下字段c0,opcode,partno,rid 字段.一定是IOT组织表的特性,限制了索引建立的长度.c0应该与表T的id相对应.
--执行如下命令:
--再次确定问题就是rebuild online,需要建立这样的IOT表,而里面包含varhcar2(4000)字段,导致出现ORA-01450: maximum key length (3215) exceeded错误.
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-732488/