5

Why in Oracle there are three logical distinct views for National Language Support ?

I can understand the need for distinguishing between session and database - to allow for client custom settings.

What about Session versus Instance ?

I ran a query to compare the values between them all, and here are the results (I noticed NLS_DATABASE_SETTINGS has more parameters all the parameters the others have plus some more, hence I am doing an outer join with it as my starting point):

SELECT
   db.parameter as parameter,
   db.value as database_value,
   s.value as session_value,
   i.value as instance_value
FROM
   nls_database_parameters db
LEFT JOIN 
   nls_session_parameters s
ON s.parameter = db.parameter
LEFT JOIN 
   nls_instance_parameters i
ON i.parameter = db.parameter
ORDER BY parameter

enter image description here

I am trying to dig deep and solve an issue of my pl/sql developer client, alone, not being able to display Hebrew characters. All the others in the company are.

Update: the issue of display characters in Hebrew (which is not the question here) was solved, following the solution I posted here.

Community
  • 1
  • 1
Veverke
  • 9,208
  • 4
  • 51
  • 95

1 Answers1

6

Nice question, I didn't know the difference between them till I did some research.
Have look at National-Language-Support

NLS_DATABASE_PARAMETERS

When you create your database, you tell it how you are going to handle or not handle globalization of the database. The NLS_DATABASE_PARAMETERS view will display what these settings were at database creation time. These are fixed at the database level and cannot be changed. The good thing is that while they do set up some of your options down the road with regard to having your database talk globally, they are only used when check constraints are enforced in the database. Therefore, you will not really need to worry about what these settings are after database creation time.

NLS_INSTANCE_PARAMETERS

As you know, you can change a variety of parameters for your instance through either the INIT.ORA file or the SPFILE. The NLS_INSTANCE_PARAMETERS view will display those settings that are set at the instance level.

NLS_SESSION_PARAMETERS

In addition, you have the ability to set each individual session's globalization parameters and the NLS_SESSION_PARAMETERS view will show you what the current settings are. This view is specific to the session querying from it.

Veverke
  • 9,208
  • 4
  • 51
  • 95
Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • 1
    @PatrickHofman because each one has its own 'work' as described above, NLS_DATABASE_PARAMETERS when the database is created , as for NLS_SESSION_PARAMETERS will show you the setting of individual setting , in my answer I covered most part. if he read the full article he will understand many things . – Moudiz Jul 21 '15 at 11:53
  • First things first: through others questions I myself had learned lots as well :-) – Veverke Jul 21 '15 at 11:56
  • 1
    Patrick, Moudiz is right, it does add to the answer. I think the question is ultimately about the difference between an instance and a session in Oracle DBs. But this definitely adds useful info. – Veverke Jul 21 '15 at 12:00