UTF8 Related


Why will a select from v$parameter shows the value of
nls_length_semantics as ‘CHAR’ while select from nls_database_parameters shows ‘BYTE’ ? 

The spfile has been verified with NLS_LENGTH_SEMANTICS as ‘CHAR’.

 Subject: The Priority of NLS Parameters Explained Doc ID: Note:241047.1

“NLS_LENGTH_SEMANTICS *cannot* be set as environment variable in 9i,

from 10g onwards it can be, please note that it needs to be set as UPPERCASE.

It is however possible to do a ALTER SESSION. If not set explicit in a session it will use the NLS_INSTANCE_PARAMETER setting.”

There are quite a few DBA views which actually get their information from other tables see the following query for why there are different values for those two different views and the above document which tells you which one is used.


select * from dba_views where view_name like ‘%NLS%’;

SYS V_$NLS_PARAMETERS 48 select “PARAMETER”,”VALUE” from v$nls_parameters

SYS V_$NLS_VALID_VALUES 65 select “PARAMETER”,”VALUE”,”ISDEPRECATED” from v$nls_valid_values

SYS GV_$NLS_PARAMETERS 59 select “INST_ID”,”PARAMETER”,”VALUE” from gv$nls_parameters

 SYS GV_$NLS_VALID_VALUES 76 select “INST_ID”,”PARAMETER”,”VALUE”,”ISDEPRECATED” from gv$nls_valid_values

 SYS NLS_SESSION_PARAMETERS 163select substr(parameter, 1, 30), substr(value, 1, 40) from v$nls_parameter

SYS NLS_INSTANCE_PARAMETERS 102 select substr(upper(name), 1, 30), substr(value, 1, 40) from v$parameter

 SYS NLS_DATABASE_PARAMETERS 76 select name,substr(value$, 1, 40) from props$where name like ‘NLS%’



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s