Systems Engineering and RDBMS

Archive for November 23rd, 2008

NLS_Database_Parameters and v$nls_parameters

Posted by decipherinfosys on November 23, 2008

We have discussed in the past about the NLS parameters including a discussion on NLS_LANG setting. One of the questions that a reader recently asked us was:

“What is the difference between v$nls_parameters and NLS_Database_Parameters and how can I see the differences in the settings for these?”

The globalization guide at otn (Oracle Technology Network) covers all these in detail and we would recommend you to go over it since this is a topic that we have seen to create a lot of confusion among DBAs and Database Developers.  NLS_DATABASE_PARAMETERS gives the permanent NLS parameters of the database where as v%nls_parameters gives the current values of the nls parameters for that session.  So, if via a logon trigger or via an “ALTER Session” command, if I change the session settings, then you will see the difference between the parameter values for the session vs what is set at the database level.

And how can you write a SQL to see all such differences?  Simple enough – do a FULL OUTER JOIN between the two and do the value comparisons:

SQL> select coalesce( perm.parameter, sess.parameter ) parameter,
2                       perm.value permanent_value,
3                       sess.value session_value
4    from nls_database_parameters perm full outer join v$nls_parameters sess on perm.parameter=sess.parameter
5   where decode(perm.value, sess.value,0,1) = 1
6  /

Posted in Oracle | 1 Comment »


Get every new post delivered to your Inbox.

Join 86 other followers