- 07 Dec, 2023
- 0 Comments
- 49 Secs Read
ORA-01450: Maximum Key Length (6398) Exceeded
Problem :
SQL> create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION,DEPT); create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION,DEPT) * ERROR at line 1: ORA-01450: maximum key length (6398) exceeded
Solution :
For a database with block size of 8K , Index length can be maximum of 6398. Lets describe the tableSQL> desc EMP_TAB Name Null? Type ----------------------------------------- -------- ------------------- EMPNO NUMBER EMPNAME VARCHAR2(3000) LOCATION VARCHAR2(3000) DEPT VARCHAR2(2000)
Here we are trying to create composite index on empname varchar2(3000) , location varchar2(3000) , dept varchar2(2000).
If we add the length 3000+3000+2000=9000 > 6398 . It is greater than the allowed value.
So to avoid this error, either we have to create the index with length less than 6398 . Else we can create a tablespace with higher block size value( like 16k/32k) and create the index in that tablespace .
SQL> create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION); Index created.Above index creation worked, because 3000 + 3000 < 6398 .