nls_session_parameters vs nls_instance_parameters vs nls_database_parameters

Question

Can somebody please explain clearly the difference between values in these tables. (mentioned in title)

I do understand that:

nls_session_parameters: sets values at session level (do these overide instance and database level)

nls_instance_parameters: sets values at instance level (what do we mean by instance level)

nls_database_parameters: sets values at whole database level.

Now if the database is of type RAC can multiple SID's have different values in above tables?

I am currently facing a problem where 'NLS_NUMERIC_CHARACTERS' gets set to wrong value in many sessions, and I cannot alter session every time. the behavior is random and very confusing

Answer 1

For 99.9% of the known universe, you are only ever going to care about session settings. Session settings always override database or instance settings. And session settings are determined by settings on the client not by anything on the server. Even when you have code that runs seemingly without a client (a job submitted via dbms_job for example), the NLS settings of the session that submitted the job will be used for executions of that job.

In a RAC cluster, there is a single database but multiple instances of that database (one per RAC node). You might be able to have different NLS settings on the different nodes of the RAC cluster (hence causing nls_instance_parameters to have different results) but that would be crazy both from the standpoint that there are essentially 0 problems that would be resolved by that and from the standpoint that running different configuration settings on different nodes of a RAC cluster is a recipe for pain, suffering, and heartache as a DBA.