Settings in Oracle client and Server

Are the settings of oracle server like , the nls_date_format,.... attributes (I don't know really how many开发者_开发问答 of these kind settings are there) , will be downloaded to the oracle client running on different machine by connecting to the SQL*plus using server host name?

Or the client will maitain its different set , assuming the client is SQL*Plus.

If the client is having its own settings ,is there any way If I can set the same settings as server. like export all settings from server and import those settings to client.

If the client is SQL Developer , is there any way to maintain the sync. between server settings and client's.

You can query the settings (about 20 variables) from the NLS-Views (NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMETERS, NLS_SESSION_PARAMETERS). By show parameter nls you can view the actual settings in SQL*Plus.

These can be altered by sysdba in several ways:

• The database parameters are read from (s)pfile while startup. They can be altered via ALTER SYSTEM SCOPE=SPFILE if you use spfile. If you use pfile you have to edit it manually
• The instance parameters are set by ALTER SYSTEM SCOPE=MEMORY and will be lost upon shutdown
• Both at once can be altered using SCOPE=BOTH. This will be applied immediately and survive the shutdown

The client can override this for his own session in (at least?) two ways:

• Set it for your session by ALTER SESSION in SQL*Plus
• Set environment variables before client startup via export NLS_...=... in the shell

The latter overrides the earlier ones, so SESSION beats INSTANCE which eats DATABASE.

For your question: you can use a SELECT * FROM NLS_INSTANCE_SETTINGS and apply these to your current session by ALTER SESSION SET ... for each of these variables (maybe via some PL/SQL procedure). Another way would be just to unset all session parameters so that the instance parameters will be used.

If you do not set any NLS variables (especially NLS_LANG) in your environment, your session should be the same as the instance or the database ones. In practice this will never work as expected as you always have some locale settings that produce weired results in SQLP*Plus. ;-)