Oracle使用Online子句创建索引报错ORA-01450: maximum key length (3215) exceeded
版本:11.2.0.4.0
生产有一张表使用online方式创建索引的时候报错,如下:
SQL> create index idx_name on test(name) online; create index idx_name on test(name) online * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded
如果不使用online子句创建则正常。
这种一般来说需要借助10046功能,由于11g默认有个10046 trace文件无法显示完整create table的bug,需要先打上补丁18705302。
详见:Oracle Trace文件的创建表(create table)语句显示不完整
实际Oracle Trace文件的创建表(create table)语句显示不完整这篇文章示例语句正是以online创建索引为例子的。
可以看出,create index ... online会内部会创建临时的索引组织表。
(PS:当然表不能是空段,也就是延迟段创建刚创建的没有数据的新表,不然即使是online也不会有IOT表产生)。
结合报错ORA-01450: maximum key length (3215) exceeded一开始还以为是存储的字段内容不能超过3215长度,实际上不是这样。
报错重现
创建测试表
由于使用了延迟段创建,即使online也不会报错。
SQL> create table test ( name varchar2(4000)); Table created. SQL> select * from user_segments where segment_name='TEST'; no rows selected SQL> create index idx_test on test (name) online; Index created. SQL> drop index idx_test; Index dropped.
插入数据申请表段,回滚,online创建索引。
可以看出即使没有数据,在存在表段的前提下online创建索引也会报错ORA-01450。
SQL> insert into test values ('a'); 1 row created. SQL> rollback; Rollback complete. SQL> select count(*) from test; COUNT(*) ---------- 0 SQL> select count(*) from user_segments where segment_name='TEST'; COUNT(*) ---------- 1 SQL> create index idx_test on test (name) online; create index idx_test on test (name) online * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded
开启10046,重新online创建索引。
SQL> select value from v$diag_info where name like '%De%'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/zkm/zkm/trace/zkm_ora_2867.trc SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> create index idx_test on test (name) online; create index idx_test on test (name) online * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded SQL> alter session set events '10046 trace name context off'; Session altered.
查看10046,可以看到创建IOT表的语句:
create table "ZKM"."SYS_JOURNAL_87429" (C0 VARCHAR2(4000), opcode char(1), partno number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE "USERS"
实际上,经过测试可以发现,IOT表的字段长度不能超过3212字节。
SQL> create table iot_test ( name varchar2(3213),primary key(name)) organization index; create table iot_test ( name varchar2(3213),primary key(name)) organization index * ERROR at line 1: ORA-01450: maximum key length (3215) exceeded SQL> create table iot_test ( name varchar2(3212),primary key(name)) organization index; Table created.
对于narchar2也同样遵循,由于1个字符长度是2字节,所以,IOT表的nvarchar2不能超过3212/2=1606。
SQL> create table iot_test (name nvarchar2(1607),primary key(name)) organization index; create table iot_test (name nvarchar2(1607),primary key(name)) organization index * ERROR at line 1: ORA-01450: maximum key length (3215) exceeded SQL> create table iot_test (name nvarchar2(1606),primary key(name)) organization index; Table created.
由于IOT表的这种限制,如果表字段本身定义的长度超过3212,则无法使用online子句创建索引。
这种情况下,只能去掉online了。去掉online的话需要注意创建索引期间会阻塞DML操作。