Systems Engineering and RDBMS

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  /

One Response to “NLS_Database_Parameters and v$nls_parameters”

  1. sumitkumar said

    SELECT a.value
    INTO l_nls_numchr
    FROM nls_database_parameters a
    i need alternative in DB2 pls help me …………………

Leave a Reply

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

You are commenting using your 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

%d bloggers like this: