Maximum key length (6398) exceeded

Products

• CA Directory
• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Risk Analytics
• CA Secure Cloud SaaS - Arcot A-OK (WebFort)
• CLOUDMINDER ADVANCED AUTHENTICATION
• CA Secure Cloud SaaS - Advanced Authentication
• CA Secure Cloud SaaS - Identity Management
• CA Secure Cloud SaaS - Single Sign On
• CA Security Command Center
• CA Data Protection (DataMinder)
• CA User Activity Reporting

Issue/Introduction

The following error is received when running the script ims_oracle_report.sql:

ERROR at line 1: ORA-01450: maximum key length (6398) exceeded

Environment

Release:
Component: IDMGR

Resolution

In addition to the key length error, there is another question/issue here besides rebuilding the database with a larger blocksize so that the script ims_oracle_report.sql can execute successfully-- the oracle optimizer is not likely to use an index with a long key.

From site
http://www.dba-oracle.com/t_ora_01450_maximum_key_length_exceeded.htm
Indexes with long index keys are rarely used!

As a general rule, indexing on very large columns (raw, long, clob) is rarely useful because the optimizer will almost always find a full-table scan cheaper than invoking an index on a long column value.

And it is not recommended to rebuild this index in a larger blocksize. While building indexes in a larger blocksize has some marginal benefits for super high-volume databases (a flatter tree structure, and faster throughput for index range scans), the Oracle CBO will almost always choose a full-table scan over an index on a very large key.

Setting nls_length_semantics in the Oracle database resolves this issue.