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?