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.
Check NLS_INSTANCE_PARAMETER.
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%’
