ORA-01450 maximum key length (3118) exceeded

All of a sudden, deploying a new version of the apps, I got this error. The fix is simple. The error is related to the block size of the tablespace where you want to deploy your object. In my case it was about the creation of an index an the key was exceeding the size of the default block size of the default tablespace. All I had to do is to enable the 16K block size and to create a new tablespace using the 16k block size.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT";
ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT"
*
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded

SQL> show parameter db_16

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
SQL> alter system set db_16k_cache_size=500M;

System altered.

SQL> CREATE TABLESPACE "NEW_TBS" DATAFILE ...... SIZE 100M BLOCKSIZE 16K;

Tablespace created.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "NEW_TBS";

Table altered.

SQL>

 

 

Advertisements