NLS_LENGTH_SEMANTICS & ORA-01450

NLS_LENGTH_SEMANTICS setting allows us to specify the length of a column in 'characters' or 'bytes' and 'byte' is it's default value.

Had one index creation failure today during a OBIEE install with ORA-01450 and this is in a Unicode AL32UTF8 database with 'nls_length_semantics=char' set.

A quick check of dba_tab_columns reveal the data_length being 4x the size of the column that's being defined due to the UNICODE characterset and the nls_length_semantics value of 'CHAR'.

SQL> select COLUMN_NAME, DATA_TYPE,DATA_LENGTH, CHAR_LENGTH,CHAR_USED      from dba_tab_columns

     where table_name='MDS_COMPONENTS';

COLUMN_NAME             DATA_TYPE       DATA_LENGTH     CHAR_LENGTH  C
-------------------------- ----------- ----------- ----------------------
COMP_COMMENT             VARCHAR2         4000               4000    C
COMP_ID                  VARCHAR2         3200                800    C
COMP_VALUE               VARCHAR2         4000               4000    C
COMP_LOCALNAME           VARCHAR2         3200                800    C
COMP_PREFIX              VARCHAR2          508                127    C
COMP_NSID                  NUMBER           22                  0
.....
.....

10 rows selected.

and a desc mds_components shows the columns being defined as VARCHAR2 (800 CHAR) due to the above settings. 

COMP_PREFIX VARCHAR2(127 CHAR)

COMP_LOCALNAME VARCHAR2(800 CHAR)

COMP_VALUE VARCHAR2(4000 CHAR)

COMP_ID VARCHAR2(800 CHAR)

COMP_COMMENT VARCHAR2(4000 CHAR)

However more interestingly an 'alter system set nls_length_semantics=BYTE scope=both' doesnt actually come into effect until the instance is restarted, though it allows 'both' in scope.

Anyway, changing the nls_length_semantics=byte finally fixed the issue with the index failure.