关于Oracle数据库NLS_LENGTH_SEMANTICS参数的修改说明:

最新推荐文章于 2026-04-10 16:00:00 发布

转载最新推荐文章于 2026-04-10 16:00:00 发布 · 734 阅读

原文:

i have unicode database (10g) with

nls_length_semantics = 'BYTE'

i try alter system set nls_length_semantics = 'CHAR';

then i try a select * from nls_instance_parameters;

now it is showing that the parameter value is CHAR.

However when i create a new table, i can see that the column size are still in bytes.

q1) how to change the whole database from byte to char

q2) will the change affect existing table columns in bytes ? and its data

*q3) if i export a schema from a database with tables define in BYTE, then i do a import into a database with nls_length_semantics = CHAR

will the tables imported be redefined as CHAR
or will it still be in bytes ?

q4) is there any sql statement whereby i can see if a table column is in byte or char.

doing a describe doesnt show

q5) i try to recreate a new session
and do a
select * from nls_session_parameters

the nls_length_semantics are still in BYTES
but from nls_instance_parameters, it is in CHAR

why is it so? do i need to bounce the db?

thanks guys!

回复:

>i try alter system set nls_length_semantics = 'CHAR';

>>actually i want the char to be default..

>>why is it so? do i need to bounce the db?



Yes



>>q2) will the change affect existing table columns in bytes ? and its data 



No



>>*q3) if i export a schema from a database with tables define in BYTE, then i do a import into a database with nls_length_semantics = CHAR



will the tables imported be redefined as CHAR

or will it still be in bytes ?



it will be be redefined as CHAR only if data are being imported to tables that have columns defined as CHAR in target database. 

That means ONLY DATA are being imported, which is BYTE in source database and converted to CHAR in target database.

However when i create a new table, i can see that the column size are still in bytes.

How did you determined this?

Few points, if NLS_LENGTH_SEMANTICS=CHAR then you would not be able to see the CHAR/VARCHAR/VARCHAR2 columns as CHAR, try changing it to BYTE then it would display you CHAR, Same may also apply for NLS_LENGTH_SEMANTICS=BYTE, that is only columns with CHAR will be displayed as CHAR but those with BYTES will not be specified. This behaviour also changes from version to version.

q1) how to change the whole database from byte to char

A1) Oracle does not support CHAR Semantics for its own components, hence SYS schema will always remain BYTE, there is not option to change this for entire database level, however if you set NLS_LENGTH_SEMANTICS to CHAR by following command, SYS Schema or Database itself will contibue to operate under BYTE semantics.

SQL> alter system set NLS_LENGTH_SEMANTICS=CHAR scope=both;

q2) will the change affect existing table columns in bytes ? and its data

A2) No, you may chose to convert them manually using alter table statements, for eg.

SQL> alter table emp modify ename varchar2(10 CHAR);

*q3) if i export a schema from a database with tables define in BYTE, then i do a import into a database with nls_length_semantics = CHAR

will the tables imported be redefined as CHAR
or will it still be in bytes ?

A3) No, by default they would be in BYTE unless you chose to modify them manually.

q4) is there any sql statement whereby i can see if a table column is in byte or char.

doing a describe doesnt show

A4) change NLS_LENGTH_SEMANTICS TO BYTE at session level and then describe the table, those that display CHAR are CHAR

SQL> alter session set nls_length_semantics=byte;

Session altered.

SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10 CHAR)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

q5) i try to recreate a new session
and do a
select * from nls_session_parameters

the nls_length_semantics are still in BYTES
but from nls_instance_parameters, it is in CHAR

A5) Are you connected as SYSDBA? Database's own objects will always remain as BYTE, hence if it is SYS schema the nls_session_parameters will always return as BYTE for SYS.

To verify the value of the parameter just type the following command

show parameter nls_length_semantics

Cheers,
Manoj

hi,

I resolved it by skiping superset check and it worked fine..

Do the steps below to convert to the charset type you require

------------when we get superset error skip superset check
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1; ---to skip superset check
SHUTDOWN IMMEDIATE;
STARTUP;

Regards
Sumit Chaudhary