By default, NLS_LENGTH_SEMANTICS is set to BYTE.
This is a problem if you didn't modify this parameter when started the database, you cannot change it afterwards online without bouncing the database.
If you can change the value for your own session (alter session) and everything works fine and you can play with the new value, if you do it on system level (alter system), the new value is not taken in account (however, the alter system command does not return any error !):
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';
NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics BYTE TRUE
SQL> alter system set nls_length_semantics=char;
System altered.
SQL> create table sysadm.nicolas(column1 varchar2(10));
Table created.
SQL> select column_name,data_type,data_length,char_length,char_used
from dba_tab_columns where table_name='NICOLAS';
COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 10 10 B
As you can see, the character used is BYTE, not CHAR as it is expected after parameter value change.
The only workaround to this, is to change the spfile and bounce the database :
SQL> alter system set nls_length_semantics=char scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2074664 bytes
Variable Size 746588120 bytes
Database Buffers 318767104 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> drop table sysadm.nicolas;
Table dropped.
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';
NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics CHAR FALSE
SQL> create table sysadm.nicolas(column1 varchar2(10));
Table created.
SQL> select column_name,data_type,data_length,char_length,char_used from dba_tab_columns where table_name='NICOLAS';
COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 30 10 C
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Now it is becoming CHAR as expected from the beginning.
I found it disapointed.
Addendum : it is an known bug #1488174 (internal bug, non-public), opened several years ago on database version 8.x, not solved yet. Find out more in the metalink note #144808.1.
