Oracle Character Set (summary)

1, database character set

The database character set consists of two character sets: the database character set and the national character set, which are specified when the database is created.

1.1, querying the character set of the current database

[Email protected]>col value for A30

[Email Protected]>select * FROM nls_database_parameters

2 where parameter in (' Nls_characterset ', ' nls_nchar_characterset ');

PARAMETER VALUE

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

Nls_characterset UTF8

Nls_nchar_characterset AL16UTF16

Parameters Nls_characterset Description Database Character set is UTF8,

Parameters Nls_nchar_characterset Description of the national character set as Al16utf16

1.2, modifying character sets

execute the following command in the Mount state of the database ;

ALTER DATABASE character set UTF8;

The command does not convert the character set inside the database, but modifies the configuration information about the character set in the database, because if it is a strict superset, then the new character set will encode the existing characters as well, because they do not need to be changed. However, if it is not a superset, an error will be made and the database can only be recreated in the new character set.

Oracle provides a non-public command:

ALTER DATABASE character Set Internal_convert <NEW_CHARACTER_SET_NAME>

This command will bypass the checksum of the hyper-subset and modify the character set configuration information directly.

2, server-side character set

The language on the server side is configured by the initialization parameter nls_language.

2.1, query Parametersnls_languatethe value

[email protected]>show parameter nls_language;

NAME TYPE VALUE

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

Nls_language string AMERICAN

2.2, ParametersNls_languagedetermine the default value for two parameters

[email protected]>show parameter nls_date_format;

NAME TYPE VALUE

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

Nls_date_format string Yyyy-mm-dd HH24:MI:SS

[Email protected]>show parameter Nls_sort

NAME TYPE VALUE

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

Nls_sort string BINARY

2.3, query Parametersnls_territorythe value

[email protected]>show parameter nls_territory;

NAME TYPE VALUE

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

Nls_territory string AMERICA

2.4, querying the data dictionaryv$nsl_valid_valuesthe different parameter values

[Email protected]>select distinct parameter from v$nls_valid_values;

PARAMETER

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

CHARACTERSET

SORT

TERRITORY

LANGUAGE

The output knows the data dictionary v$nls_valid_values can query the available values for the following parameters:

CHARACTERSET (Database character set)

SORT (Sort method)

TERRITORY (area)

LANGUAGE (language)

3, client Character set3.1, Introduction

On the client, define the client's character set by setting environment variables. The environment variable is defined as follows:

Nls_lang=<language>_<territory>.<client character set>

1) <language>: used to display the name of the Oracle message, year and year (for example , whether the month should show "Dec" or " month"), and the default sorting mechanism. The initialization parameter for the corresponding server is nls_language.

2)<terriory>: used to display the default date format, number format, and currency format, the corresponding server initialization parameter is nls_territory.

3)<client character set>: The character set used by the client and the initialization parameter for the corresponding server is nls_characterset.

3.2, specific Operation

(1) at the operating system prompt, set the Nls_lang variable to American_america.utf8 and Adjust the default date display.

in the a command such as the following can be used in Windows systems:

C:/>set Nls_lang=american_america.utf8

C:/>set nls_date_format=day dd Month yyyy

or in The Unix system uses commands such as the following:

Export Nls_lang=american_america.utf8

Export nls_date_format= ' Yyyy-mm-dd HH24:MI:SS '

(2) start in the same operating system session Sql*plus, and is connected as user SYSTEM .

(3) Use the following command to display the current date:

Select Sysdate from dual;

3.3, QueryOracleserverend of the character set

Sql> Select Userenv (' language ') from dual;

USERENV (' LANGUAGE ')

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

Simplified Chinese_china. UTF8

4,Oraclecharacter Set related queries4.1, QueryOracleSupported Languages

[Email protected]>select value from v$nls_valid_values whereparameter= ' LANGUAGE ';

VALUE

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

AMERICAN

4.2, QueryOraclesupported Regions

[Email protected]>select value from v$nls_valid_values whereparameter= ' TERRITORY ';

VALUE

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

AMERICA

4.3, QueryOraclesupported database character sets

[Email protected]>select value from v$nls_valid_values whereparameter= ' CHARACTERSET ';

4.4, database server character Set environment

SELECT * FROM Nls_database_parameters

4.5, client character Set environment

SELECT * FROM Nls_instance_parameters

4.6, session character set environment

select* from Nls_session_parameters

This article is from the "MySQL" blog, be sure to keep this source http://wenjing.blog.51cto.com/5908329/1700676

Oracle Character Set (summary)