Maximum key length (6398) exceeded

book

Article ID: 4026

calendar_today

Updated On:

Products

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 SECURITY MISC CODES SINGLE SIGN ON - LEGACY 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.