denrepo

Init parameterCompatibility

NLS_LENGTH_SEMANTICS

Changes immediatelyCan set per session

What it controls

Whether VARCHAR2 lengths in new columns default to bytes or characters.

Know this before you change it

With a multibyte character set, CHAR avoids ORA-12899 on non-ASCII data. It only affects columns created afterwards.

Default: BYTE

Check and change it

nls_length_semantics.sql 
1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'nls_length_semantics';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'nls_length_semantics' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET nls_length_semantics = CHAR SCOPE = BOTH SID = '*';11 12-- Or for your own session only13ALTER SESSION SET nls_length_semantics = CHAR;14 15-- Remove it from the spfile to go back to the default at the next restart16ALTER SYSTEM RESET nls_length_semantics SCOPE = SPFILE SID = '*';

Open in the parameter referenceOracle's reference

More in Compatibility