What is the maximum column varchar size for Composite columns in Oracle

Question

I am trying something like below and getting exception.

CREATE TABLE test1( col1 varchar(4000) NOT NULL, col2 varchar(4000) NOT NULL, col3 varchar(4000), col4 varchar(4000), col5 varchar(4000) NOT NULL, col6 varchar(4000), col7 varchar(4000) , PRIMARY KEY(col1, col2, col3))

Oracle version => 19.16.0.0.0

exception: Error report - ORA-01450: maximum key length (6398) exceeded 01450. 00000 - "maximum key length (%s) exceeded" *Cause:
*Action:

Answer 1

This blog post was written to clarify this specific problem. This is the gist of it:

The smallest unit of data storage in Oracle Database is the block. This defaults to 8k (8,192 bytes).

Each index entry must fit within one block. So the maximum size of an indexed value must be less than the block size. Minus some storage overheads. In a default installation this means the largest indexable value is around 6,400 bytes.

Since your primary key is enforcing a UNIQUE key (by the creation of a UNIQUE index), it also needs to fit into a single block.

You cannot change the block size of an already created database, see: https://docs.oracle.com/cd/E24693_01/server.11203/e24448/initparams049.htm and https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:226813934564

So, if you really, really want (I do not recommend this) to increase your block size, then you can export your database, create a new database with the desired block size and import the exported database.

However, I strongly recommend the reconsideration of this. Having three textual fields as the primary key spells disaster for performance because each time you search by your primary key fields values will be compared to very long texts. Hence, it would make much more sense to create a numeric field as the primary key and maybe a unique index on your three fields if you change the block size or, if possible, implement a cron job which would periodically check for new records whether they fulfill the uniqueness you expect to ease the burden on your RDBMS.