Index failed to create with ORA-01450
Trying to create an Index when the tablespace is of 8k block size
SQL> CREATE INDEX PSOPSPMTRANSHIST ON PSPMTRANSHIST (PM_TRANS_DEFN_ID, PM_CONTEXT_VALUE1, PM_CONTEXT_VALUE2, PM_METRIC_VALUE1, PM_METRIC_VALUE7, PM_AGENT_STRT_DTTM) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING; CREATE INDEX PSOPSPMTRANSHIST ON PSPMTRANSHIST (PM_TRANS_DEFN_ID, PM_CONTEXT_VALUE1, PM_CONTEXT_VALUE2, PM_METRIC_VALUE1, PM_METRIC_VALUE7, PM_AGENT_STRT_DTTM) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING * ERROR at line 1: ORA-01450: maximum key length (6398) exceeded
Version:-
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL>
Related Bugs from MOS:
This is due to Unpublished Bug 8501924: “ERRORS INSTALLING DV WITH 8192 BLOCK SIZE” ( fixed in 11.2.0.3)
Workaround:-
The workaround is to manually create the failed constraint and force the index to be created in a tablespace with a 16K block size.
1) Create a tablespace with a block size of 16K
SQL> show parameter db_16k_cache_size NAME TYPE VALUE ------------------------------------ --------------- ------------------------------ db_16k_cache_size big integer 0 SQL> SQL> alter system set db_16k_cache_size=100m; System altered. SQL> show parameter db_16k_cache_size NAME TYPE VALUE ------------------------------------ --------------- ------------------------------ db_16k_cache_size big integer 112M SQL>
Create Tablespace with 16k Block size
SQL> create tablespace psindex_16k datafile '/u01/oradata/p91phcc/psindex16k_01.dbf' size 1000m blocksize 16k AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO; Tablespace created. SQL>
— normal user connected —-
Now create Index by mentioning tablespaces of 16k block size. SQL> CREATE INDEX PSOPSPMTRANSHIST ON PSPMTRANSHIST (PM_TRANS_DEFN_ID, PM_CONTEXT_VALUE1, PM_CONTEXT_VALUE2, PM_METRIC_VALUE1, PM_METRIC_VALUE7, PM_AGENT_STRT_DTTM) tablespace psindex_16k STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING; Index created. SQL>
Check Index Status
SQL> select index_name,owner,status from dba_indexes where index_name='PSOPSPMTRANSHIST'; INDEX_NAME OWNER STATUS ------------------------------ ------------------------------ -------------------------------- PSOPSPMTRANSHIST SYSADM VALID SQL>
