Differences between Oracle v $ nls_parameters and nls_database_parameters

 

I. First read the instructions on the official website.

1.1 V $ nls_parameters

V $ nls_parameters contains current values of NLS parameters.

 

 

 

 

 

Column	Datatype	Description
Parameter	Varchar2 (64)	Parameter names are as follows: nls_calendar, latency, nls_comp, nls_currency, nls_date_format, nls_date_language, latency, latency, nls_language, latency, nls_sort, nls_territory, latency   Two additional parameters, nls_time_format and nls_time_tz_format, are currently used for internal purposes only.
Value	Varchar2 (64)	NLS parameter value

 

 

1.2 nls_database_parameters

Nls_database_parameters lists permanent NLS parameters of the database.

 

 

 

 

 

Column	Datatype	Null	Description
Parameter	Varchar2 (30)	Not null	Parameter Name
Value	Varchar2 (40)	Parameter Value

 

Definition from the official website:

Nls_database_parameterslists permanent NLS parameters of the database.

V $ nls_parameterscontains current values of NLS parameters.

 

We can see that:

(1) nls_database_parameters comes from props $, which indicates the character set of the database. It is set during database creation and will not be changed.

(2) V $ nls_parameters displays the current session value, which is controlled by the NLS client.

 

Therefore, the query results of the two views may be different.

 

Other related views:

(1) nls_instance_parameters is derived from V $ parameter, which indicates the character set setting of the client, which may be a parameter file, environment variable or registry.

(2) nls_session_parameters comes from V $ nls_parameters, indicating the session's own settings. It may be the session's environmental variable or the session is completed by alter session. If the session does not have special settings, it will be consistent with nls_instance_parameters.

 

 

Ii. Example

Let's take the character set as an example to view and compare it.

 

View and modify Oracle Character Set

Http://blog.csdn.net/tianlesoftware/article/details/4915223

 

The format of nls_lang is as follows: nls_lang = language_territory.charset

Language: Specifies the language of the server message, which affects whether the prompt information is in Chinese or English.

Territory: Specifies the date and number format of the server,

Charset: Specifies the character set, which truly affects the database character set.

 

For example: American _ America. zhs16gbk

 

Sys @ dave2 (DB2)> select * From nls_database_parameters where parameter in ('nls _ type', 'nls _ territory ', 'nls _ characterset ');

 

Parameter Value

-------------------------------------------------------------------------------

Nls_language American

Nls_territory America

Nls_characterset zhs16gbk

 

Sys @ dave2 (DB2)> select * from V $ nls_parameters where parameter in ('nls _ type', 'nls _ territory ', 'nls _ characterset ');

 

Parameter Value

-------------------------------------------------------------------------------

Nls_language American

Nls_territory America

Nls_characterset zhs16gbk

 

Now, the query results of nls_database_parameters and V $ nls_parameters are the same. Now let's modify the character set. Then, View:

 

 

Sys @ dave2 (DB2)> alter system setnls_language = 'simplified Chinese 'scope = spfile;

System altered.

-- This parameter takes effect only after being restarted.

 

Sys @ dave2 (DB2)> shutdown immediate

Database closed.

Database dismounted.

Oracle instance shut down.

Sys @ dave2 (DB2)> startup

Oracle instance started.

 

Total system global area 239075328 bytes

Fixed size 1218724 bytes

Variable Size 79693660 bytes

Database buffers 155189248 bytes

Redo buffers 2973696 bytes

Database mounted.

Database opened.

 

Sys @ dave2 (DB2)> select * From nls_database_parameters where parameter in ('nls _ type', 'nls _ territory ', 'nls _ characterset ');

 

Parameter Value

-------------------------------------------------------------------------------

Nls_language American

Nls_territory America

Nls_characterset zhs16gbk

 

Sys @ dave2 (DB2)> select * fromv $ nls_parameters where parameter in ('nls _ type', 'nls _ territory ', 'nls _ characterset ');

 

Parameter Value

-------------------------------------------------------------------------------

Nls_language Simplified Chinese

Nls_territory America

Nls_characterset zhs16gbk

 

Now we can see that the results of the two views are different.

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 chat group: 40132017 chat group 2: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.