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.

Comments