oracle服务器和客户端字符集的查看和修改

[Q]怎么样查看数据库字符集 

　　[A]数据库服务器字符集select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。

　　客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，

　　表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表

　 　会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。

　　客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件

　　字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

　　[Q]怎么样修改字符集 

　　[A]8i以上版本可以通过alter database来修改字符集，但也只限于子集到超集，不建议修改props$表，将可能导致严重错误。

　　Startup nomount;

　　Alter database mount exclusive;

　　Alter system enable restricted session;

　　Alter system set job_queue_process=0;

　　Alter database open;

　　Alter database character set zhs16gbk;

 

注：

1.通过alter system set nls_language='AMERICAN' scope=spfile; 可以更改nls_database_parameters和nls_instance_parameters的nls_language

2. 通过alter session set nls_language='SIMPLIFIED CHINESE'; 可以更改nls_session_parameters的nls_language

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

  sqlplus连接oracle乱码 

RedHat linux AS4.0安装了oracle10g，同时创建数据库实例sc，后来又用dbca建立一个数据库实例st，发现在oracle系统用户下用sqlplus 连接st实例时出现乱码，应该是中文无法显示造成的，但连接sc实例时无乱码，显示英文。上网搜集了一下资料，修改了oracle系统用户的环境变量 在.bash_profile中新增了以下内容解决：

export NLS_LANG=AMERICAN_AMERICA.UTF8 
export LANG=en_US.UTF-8

 

我在centos5.5（支持中文）上只设置了【export NLS_LANG=AMERICAN_AMERICA.UTF8】就解决了sqlplus上的乱码问题了。

 

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

This section explains the order in which NLS parameters are taken into account in the database client/server model. (This does NOT cover Thin JDBC connections)

There are 3 levels at which you can set NLS parameters: Database, Instance and Session. 
If a parameter is defined at more than one level then the rules on which one takes precedence are quite straightforward:

1. NLS database settings are superseded by NLS instance settings
2. NLS database & NLS instance settings are superseded by NLS session settings

 

 

Session Parameters

SELECT * from NLS_SESSION_PARAMETERS;

These are the settings used for the current SQL session.

These reflect (in this order):

1) The values of NLS parameters set by "ALTER SESSION "

ALTER SESSION set NLS_DATE_FORMAT = 'DD/MM/YYYY';

2) If there is no explicit "ALTER SESSION " statement done then it reflects the setting of the corresponding NLS parameter on the client derived from the NLS_LANG variable.

3) If NLS_LANG is specified with only the <Territory> part then AMERICAN is used as default <Language>.

So if you set NLS_LANG=_BELGIUM. WE8MSWIN1252 then you get this:

PARAMETER VALUE

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

NLS_LANGUAGE AMERICAN

NLS_TERRITORY BELGIUM

NLS_CURRENCY <euro sign here>

NLS_ISO_CURRENCY BELGIUM

....

Note:

The difference between NLS_LANG=_BELGIUM.WE8MSWIN1252 (correct) and

NLS_LANG=BELGIUM.WE8MSWIN1252 (incorrect), you need to set the "_" as separator.


4) If NLS_LANG is specified with only the <Language> part then the <Territory> defaults to a setting based on <Language>.

So if you set NLS_LANG=ITALIAN_.WE8MSWIN1252 then you get this:

PARAMETER VALUE

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

NLS_LANGUAGE ITALIAN

NLS_TERRITORY ITALY

NLS_CURRENCY <euro sign here>

NLS_ISO_CURRENCY ITALY

.....

Note:

Note the difference between NLS_LANG=ITALIAN_.WE8MSWIN1252 (correct) and

NLS_LANG=ITALIAN.WE8MSWIN1252 (incorrect), you need to set the "_" as separator.


5) If NLS_LANG is specified without the <Language>_<Territory> part then the <Language>_<Territory> part defaults to AMERICAN_AMERICA.

So if you set NLS_LANG=.WE8MSWIN1252 then you get this:

PARAMETER VALUE

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

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

....

Note:

The difference between NLS_LANG=.WE8MSWIN1252 (correct) and

NLS_LANG=WE8MSWIN1252 (incorrect), you need to set the "." as separator.


6) If the NLS_LANG is set (either like in point 3, 4 or 5) then parameters like

NLS_SORT, NLS_DATE_FORMAT, etc. can be set as a "standalone" setting and will overrule the defaults derived from NLS_LANG <Language>_<Territory> part.

So if you set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 and NLS_ISO_CURRENCY=FRANCE then you get this:

PARAMETER VALUE

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

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY FRANCE

...

Defaults:

---------

* If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from

NLS_LANGUAGE.

* If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are not set then they are derived from NLS_TERRITORY

7) If the NLS_LANG is not set at all, then it defaults to

<Language>_<Territory>.US7ASCII and the values for the

<Language>_<Territory> part used are the ones found in

NLS_INSTANCE_PARAMETERS. Parameters like NLS_SORT defined as "standalone" on the client side are ignored.

Note:

* If set, client parameters (NLS_SESSION_PARAMETERS) always take precedence over NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS.

* This behavior cannot be disabled on/from the server, so a parameter set on the client always has precedence above an instance or database parameter.

* NLS_LANG cannot be changed by ALTER SESSION, NLS_LANGUAGE and NLS_TERRITORY can. However NLS_LANGUAGE and /or NLS_TERRITORY cannot be set as "standalone" parameters in the environment or registry on the client.

* NLS_SESSION_PARAMETERS is NOT visible for other sessions. If you need to trace this then you have to use a logon trigger to create your own logging table (based on session parameters)

* The <clients characterset> part of NLS_LANG is NOT shown in any system table or view.

* On Windows you have two possible options, normally the NLS_LANG is set in the registry, but it can also be set in the environment, however this is not often done and generally not recommended to do so. The value in the environment takes precedence over the value in the registry and is used for ALL Oracle_Homes on the server if defined as a system environment variable.

* NLS_LANGUAGE in the session parameters also declares the language for the client error messages.

* You cannot "set" a NLS parameter in an SQL script; you need to use ALTER SESSION.


Top of the Document

Instance Parameters

SELECT * from NLS_INSTANCE_PARAMETERS;

These are the settings in the init.ora of the database at the moment that the database was started or set through ALTER SYSTEM.

If the parameter is not explicitly set in the init.ora or defined by ALTER SYSTEM then its value is NOT derived from a "higher" parameter (we are talking about parameters like NLS_SORT that derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS, this is NOT the case for NLS_INSTANCE_PARAMETERS)

Note:

* NLS_LANG is not an init.ora parameter; NLS_LANGUAGE and NLS_TERRITORY are so you need to set NLS_LANGUAGE and NLS_TERRITORY separately.

* You cannot define the <clients characterset> or NLS_LANG in the init.ora

The client characterset is defined by the NLS_LANG on the client OS (see above).

* You cannot define the database characterset in the init.ora. The database characterset is defined by the "Create Database" command.

* These settings take precedence above the NLS_DATABASE_PARAMETERS.

* These values are used for the NLS_SESSION_PARAMETERS if the client the

NLS_LANG is NOT set.

* Oracle strongly recommends that you set the NLS_LANG on the client at least to

NLS_LANG=.<clients characterset>

* The NLS_LANGUAGE in the instance parameters also declares the language for the server error messages in alert.log and in trace files.


Top of the Document

Database Parameters

SELECT * from NLS_DATABASE_PARAMETERS;

Defaults to AMERICAN_AMERICA if there are no parameters explicitly set in the init.ora during database creation time. If there is parameters set in the init.ora during database creation you see them here. There is no way to change these after the database creation. Do NOT attempt to update system tables to bypass these settings! These settings are used to give the database a default if the INSTANCE and SESSION parameters are not set.

Note:

* NLS_LANG is not an init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.

So you need to set NLS_LANGUAGE and NLS_TERRITORY separately.

* These parameters are overridden by NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS.

* You cannot define the <clients character set> or NLS_LANG in the init.ora. The client character set is defined by the NLS_LANG on the client OS.

* You cannot define the database character set in the init.ora.

The database (national) character set NLS_(NCHAR)_CHARACTERSET) is defined by the "Create Database" command.

* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be overridden by instance or session parameters.

They are defined by the value specified in the "CREATE DATABASE command and are not intended to be changed afterwards dynamically. Do NOT update system tables to change the character set. This can corrupt your database and potentially make it impossible to open the database again.

* Setting the NLS_LANG during the creation of the database does not influence the NLS_DATABASE_PARAMETERS.

* The NLS_LANG set during the database creation has NO impact on the database National Characterset.


Additional SELECT statements:

A) SELECT name,value$ from sys.props$ where name like '%NLS%';

This gives the same info as NLS_DATABASE_PARAMETERS.

You should use NLS_DATABASE_PARAMETERS instead of props$.

Note the UPPERCASE '%NLS%'

B) SELECT * from v$nls_parameters;

This view shows the current session parameters and the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.

C) SELECT name,value from v$parameter where name like '%NLS%';

This view gives the same information as NLS_INSTANCE_PARAMETERS.

Note the LOWERCASE '%NLS%'

D) SELECT userenv ('language') from dual;

and

SELECT sys_context('userenv','language') from dual;

Both these SELECT statements give the session's <Language>_<territory> and the

DATABASE character set. The database character set is not the same as the character set of the NLS_LANG that you started this connection with! So don't be fooled, although the output of this query looks like the value of a NLS_LANG variable, it is NOT.

E) SELECT userenv ('lang') from dual;

This SELECT gives the short code that Oracle uses for the Language defined by NLS_LANGUAGE setting for this session. If NLS_LANGUAGE is set to French then this will return "F", if NLS_LANGUAGE is set to English then this will return "GB"

If NLS_LANGUAGE is set to American then this will return "US", and so on...

F) SHOW parameter NLS%

This will give the same as the NLS_INSTANCE_PARAMETERS