The following error is received when running the script ims_oracle_report.sql:
ERROR at line 1: ORA-01450: maximum key length (6398) exceeded
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.
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.