ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120612]ORA-01450: maximum key length (3215) exceeded .txt

[20120612]ORA-01450: maximum key length (3215) exceeded .txt

原创 Linux操作系统 作者:lfree 时间:2012-06-11 17:40:20 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2323
  • 访问量
    6050878