- 07 Dec, 2023
- 0 Comments
- 49 Secs Read
ORA-01450: Maximum Key Length (6398) Exceeded
Problem :
1 2 3 4 5 | 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 table1 2 3 4 5 6 7 | SQL> desc EMP_TABName Null? Type----------------------------------------- -------- -------------------EMPNO NUMBEREMPNAME 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 .
Above index creation worked, because 3000 + 3000 < 6398 .
1 2 | SQL> create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION);Index created. |