11.2.0.2 and onwards: v$parameter reflects the NLS session parameters, not the NLS instance parameters

After a migration of a database from single-byte to multi-byte character set, I wanted to compare the nls_parameters.

As a part of the migration, we had to change semantics on several of our tables from BYTE to CHAR to accommodate for expanding data.

One issue that came up during our initial discussions on how to complete the migration, was if it would be beneficial to set nls_length_semantics to CHAR in the init.ora file. But according to Oracle's documentation, the NLS_LENGTH_SEMANTICS should be kept to BYTE at database level.(Check Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)", section G: "Summary of best practices") so that idea was rejected.

After the import was finished, I wanted to compare the parameters to quality check the new instance.

I queried the v$parameter view, as many DBAs would normally do and found that nls_length_semantics seemed to have been set to CHAR at the instance level:

select name,value from v$parameter where name = 'nls_length_semantics';

NAME	VALUE
nls_length_semantics	CHAR

After a quick search on My Oracle Support I found Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"

Turns out that this is not an error, but the result of a corrected bug (bug no 8722860).

In previous releases, a query of v$parameter or "show parameter" in sql*plus would reflect the instance parameters.
This is incorrect, as Oracle points out:

The documentation set states that :
* the SQL*Plus command show parameter displays the values of initialization parameters in effect for the current session.
* the V$PARAMETER view displays information about the initialization parameters that are currently in effect for the session


As Oracle puts it
"From 11.2.0.2 onwards V$PARAMETER and the SQL*Plus command show parameter reflect the session NLS settings as it should."

So make sure to use v$system_parameter instead, which is reflecting the correct instance setting:

select name,value from V$SYSTEM_PARAMETER where name = 'nls_length_semantics';

NAME	VALUE
nls_length_semantics	BYTE

Sources:
Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)"
Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"
Doc ID 241047.1: "The Priority of NLS Parameters Explained (Where To Define NLS Parameters)