ORA-01450: maximum key length (6398) exceeded
ORA-01450: maximum key length (3218) exceeded
or
ORA-01450: maximum key length (6398) exceeded
> Create table employee
(
Emp_no varchar2(4000),
Emp_name varchar2(4000),
Dept_no varchar2(4000)
)
TABLESPACE SMALL_DATA;
>insert into employee values('1','Munna','10');
>insert into employee values('2','Sunny','30');
>commit;
>create index idx_emp on employee(emp_no,emp_name,dept_no)
TABLESPACE SMALL_INDEX;
>create index idx_emp on employee(emp_no,emp_name,dept_no)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
Solution 1:
Use a smaller index key. There is a restriction on index data length. It depends on the db_block_size. It is 3218 on 8i database but 6398 is on 9.2, 10g and 11g database when the block size is 8k.
>drop table employee;
Table dropped.
> Create table employee
(
Emp_no varchar2(3000),
Emp_name varchar2(3000),
Dept_no varchar2(300)
)
TABLESPACE SMALL_DATA;
Table created.
> create index idx_emp on employee(emp_no,emp_name,dept_no)
TABLESPACE SMALL_INDEX;
Index created.
Solution 2:
Recreate the database with a larger db_block_size.
Solution 3:
If it’s not possible to change DB_BLOCK_SIZE then only other option would be to create a tablespace with nonstandard block size and create the corresponding index on that tablespace.
Example:
> show parameter db_block_
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
> show parameter db_cache
NAME TYPE VALUE
----------------- --------------------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0
> alter system set db_16K_cache_size=4M scope=both;
System altered.
> create tablespace TMP_TEST16K datafile '+DATA' size 20M blocksize 16k;
Tablespace created.
>ALTER USER REP QUOTA UNLIMITED ON TMP_TEST16K;
User altered.
>drop table employee;
Table dropped.
>Create table employee
(
Emp_no varchar2(4000),
Emp_name varchar2(4000),
Dept_no varchar2(4000)
)
TABLESPACE TMP_TEST16K;
Table created.
>insert into employee values('1','Munna','10');
>insert into employee values('2','Sunny','30');
>commit;
>create index idx_emp on employee(emp_no,emp_name,dept_no)
TABLESPACE TMP_TEST16K;
Index created.