ORA-01450: Maximum Key Length (12958) Exceeded

• While creating index in database got ORA-01450: maximum key length (12958) exceeded.

> create index idx_emp on employee(emp_no,emp_name,dept_no)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

•  Upon searching found: ORA-01450 When Creating an Index (Doc ID 293599.1)
  
  According to MOS note, There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size,it is 6398. Also the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter. That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage.
  
  
• We have 2 ways to solve this
  
  Solution 1:
  
  Use a smaller index key. There is a restriction on index data length. It depends on the db_block_size. (You can refer ORA-01450 and Maximum Key Length - How it is Calculated (Doc ID 136158.1)
  
  Solution 2:
  
  Create a tablespace with nonstandard block size and create the corresponding index on that tablespace.
  
  Check current DB_BLOCK_SIZE

> show parameter db_block_

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_block_buffers                     integer                           0
db_block_checking                    string                            FALSE
db_block_checksum                    string                            TYPICAL
db_block_size                        integer                           16384

Check current DB_CACHE

> show parameter db_cache
NAME TYPE VALUE
----------------- -------------- ------------
db_cache_advice string ON
db_cache_size big integer 0

Set DB_nK_CACHE_SIZE parameter

> alter system set db_32K_cache_size=500M scope=both;
System altered.

Create a tablespace with nonstandard block size 

> create tablespace TMP_TEST32K datafile '+DATA' size 1G autoextend on blocksize 32k;
Tablespace created. 

Create the index using the created tablespace

>create index idx_emp on employee(emp_no,emp_name,dept_no) tablespace TMP_TEST32K;
Index created.