nls_database_parameters tips

Question:  What does the nls_database_parameters do?  Can you show a query using nls_database_parameters?

Answer:  The nls_database_parameters shows the values of the NLS parameters for the database. Oracle notes these differences between the parameters.

• NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.
• NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
• NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.

The NLS_LANGUAGE and NLS_TERRITORY values in  nls_database_parameters cannot be changed once the database has been created (they come from init.ora/spfile at the time of CREATE DATABASE statement).

The values are stored in the database. Here is a SQL query using nls_database_parameters:

select
   d.parameter Dparameter,
   i.parameter Iparameter,
   s.parameter Sparameter
from
   nls_database_parameters d,
   nls_instance_parameters i,
   nls_session_parameters s
where
   d.parameter = i.parameter (+)
and
   d.parameter = s.parameter (+)
order by 1;

======================================

This query will compare the values in nls_database_parameters with v$nls_parameters:

select
   coalesce( perm.parameter, sess.parameter ) parameter,
   perm.value permanent_value,
   sess.value session_value
 from
   nls_database_parameters perm
full outer join
   v$nls_parameters sess
on
   perm.parameter=sess.parameter
where
   decode(perm.value, sess.value,0,1) = 1;