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.
