0

I am using Oracle 19 and recently changed the collation to XGERMAN_AI. This required to change the MAX_STRING_SIZE to be EXTENDED as in the following link https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321

Now, trying to create tables with unique constraints on varchar2 columns produce the error

ORA-01450: maximum key length (6398) exceeded.

Here is my table

CREATE TABLE MY_TABLE (
    ID NUMBER(38, 0) DEFAULT PKS_MY_TABLE_SEQ.nextval NOT NULL, 
    VERSION NUMBER(38, 0) DEFAULT 0 NOT NULL, 
    CREATED_BY VARCHAR2(50 CHAR) NOT NULL, 
    CREATED_AT TIMESTAMP NOT NULL, 
    MODIFIED_BY VARCHAR2(50 CHAR) NOT NULL, 
    MODIFIED_AT TIMESTAMP NOT NULL, 
    ID_OLD NUMBER(38, 0), 
    RISK_LEVEL VARCHAR2(255 CHAR) NOT NULL, 
    REMARKS VARCHAR2(255 CHAR), 
    IS_DEACTIVATED NUMBER(1) DEFAULT 0 NOT NULL, 
    COLOR VARCHAR2(255 CHAR) NOT NULL, 
    CONSTRAINT MY_TABLE_PK PRIMARY KEY (ID), 
    UNIQUE (RISK_LEVEL)
)

I googled this error, and all results say that it is becase the index size is bigger than the block size which is 8k. But in my case the column RISK_LEVEL is only 255 char long! (255*4 bytes = 1020 bytes), which is much less than 8k and also 6398

Any idea how to fix this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.