Oracle database Character Set learning notes

One, what is the Oracle character set

The Oracle character set is based on a certain character encoding scheme, which assigns a set of different numerical codes to a specific group of symbols.

The most important parameter that affects the Oracle database character set is the Nls_lang parameter.
Its format is as follows: Nls_lang = Language_territory.charset
It has three components (language, region, and character set), and each component controls the characteristics of the NLS subset.
which

Language: Specifies the language of the server message, which indicates whether the message is Chinese or English
Territory: Specify date and number formats for the server
Charset: Specifying a character Set
such as: American_america. Zhs16gbk
From the composition of Nls_lang we can see that the real impact of the database character set is actually the third part.
So the character set between the two databases as long as the third part of the same can be imported to export data, before the impact of only the hint information is Chinese or English.

is the Nls_lang parameter a Nls_lang in an environment variable?

Not. The Nls_lang in the Oracle environment variable is the character set that defines the client.
such as environment variable Nls_lang=american_america. ZHS16GBK but the character set of the database three instances can be: American_america. Al32utf8, American_america. WE8ISO8859P1, American_america. Zhs16gbk

National character set and database character set

The Oracle database is divided into the national character set (character set) and the database character set (db character set). Both are set when you create the database. The national character set is primarily used for field data for nchar, NVARCHAR, NCLOB types, and database character sets are used extensively for: CHAR, VARCHAR, CLOB, field data of type long.
and the national character set can only be al16utf16 or UTF8, generally with AL16UTF16.

Second, character set naming rules

Oracle's character set naming follows the following naming rules:
<language><bit size><encoding>
:< language >< bit >< coding >
For example: ZHS16GBK represents the use of GBK encoding format, 16-bit (two-byte) Simplified Chinese character set.

Third, the character set query

Service side:

Select Userenv (' language ') from dual;

Client:

Echo $NLS _lang

National Character Set:

SELECT * from V$nls_parameters;

Nls_characterset is the service-side character set, Nls_nchar_characterset is the national character set.

Four, Character set changes

Changes to the Oracle character set can only be made to a subset to the superset so that there is no compatibility problem. For example, WE8ISO8859P1 and ZHS16GBK are not subsets and superset of the relationship.
Therefore, it is best to specify a good character set when the database is created.

To modify the character set method:

For example, change the character set of an instance from WE8ISO8859P1 to ZHS16GBK.

Sql>shutdown immediate;
Sql>startup Mount;
Sql>alter system enable restricted session;
Sql>alter system set job_queue_processes=0;
Sql>alter system set aq_tm_processes=0;
Sql>alter database open;
Sql>alter database character Set ZHS16GBK;
Sql>alter Database national character set ZHS16GBK;
Sql>shutdown immediate;
sql>startup;

Execution process:

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup Mount;
ORACLE instance started.

Total System Global area 281018368 bytes
Fixed Size 2020160 bytes
Variable Size 96472256 bytes
Database buffers 176160768 bytes
Redo buffers 6365184 bytes
Database mounted.
Sql> alter system enable restricted session;

System altered.

Sql> alter system set job_queue_processes=0;

System altered.

Sql> alter system set aq_tm_processes=0;

System altered.

sql> ALTER DATABASE open;

Database altered.

Sql> ALTER DATABASE character set ZHS16GBK;
ALTER DATABASE character Set ZHS16GBK
*
ERROR at line 1:
Ora-12712:new Character set must is a superset of old character set

Use Internal_use to cast.

Sql> ALTER DATABASE character set Internal_use ZHS16GBK;

Database altered.

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> startup;
ORACLE instance started.

Total System Global area 281018368 bytes
Fixed Size 2020160 bytes
Variable Size 96472256 bytes
Database buffers 176160768 bytes
Redo buffers 6365184 bytes
Database mounted.
Database opened.