Oracle Database Character Set issues

The Oracle database character set involves three aspects:

1. Oracle Server-side character set

2. Oracle client-side character set

3. DMP file Character Set

   
   

   
   

The first thing you need to know is that the parameters that affect the database character set are Nls_lang

Format Nls_lang = Language_territory.charset consists of three parts, language, geography, and character set

Language: Specifies the language of the server message, whether the effect prompt is Chinese or English

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

Charset: Specifying a character Set

Therefore, the character set between the two databases can import and export data to each other as long as the third part, the first effect is whether the message is Chinese or English.

One

View the database server character set

SELECT * FROM Nls_database_parameters

Modifying the server-side character set (not recommended)

1. Close the database

Sql>shutdown IMMEDIATE

2. Boot to mount

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;

--Here you can get from the parent set to the subset

Sql>alter DATABASE CHARACTER SET ZHS16GBK;

Sql>alter DATABASE National CHARACTER SET al16utf16;

--If you are from a subset to a parent set, you need to use the Internal_use parameter to skip the hyper-subset detection

Sql>alter DATABASE CHARACTER SET internal_use Al32utf8;

Sql>alter DATABASE National CHARACTER SET internal_use al16utf16;

Sql>shutdown IMMEDIATE;

Sql>startup

Note: If there are no large objects, there is no effect on language conversion during use (remember that the set must be supported by Oracle, otherwise you cannot start) as you have done.

If the ' Ora-12717:cannot ALTER DATABASE national CHARACTER SET when NCLOB data exists ' message appears,

There are two ways to solve this problem

1. Use the Internal_use keyword to modify the regional settings,

2. Use re-create, but re-create is a bit complicated, so please use Internal_use

Sql>shutdown IMMEDIATE;

Sql>startup MOUNT EXCLUSIVE;

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 National CHARACTER SET internal_use UTF8;

Sql>shutdown immediate;

sql>startup;

If you do this, the national charset locale is fine.

Two

Viewing the client character set

SELECT * FROM Nls_instance_parameters

Represents the setting of the client's character set, which may be a parameter file, an environment variable, or a registry

Under the Windows platform, it is the Nls_lang of the corresponding oraclehome in the registry. You can also set it in the DOS window itself,

For example: Set Nls_lang=american_america. Zhs16gbk

This will only affect the environment variables in this window.

Under the UNIX platform, it is the environment variable Nls_lang.

$echo $NLS _lang

American_america. Zhs16gbk

If the result of the check finds that the server side is inconsistent with the client-side character set, uniformly modify the same character set as the server side.

To modify the client character set:

1) UNIX Environment

$NLS _lang= "Simplified Chinese" _china.zhs16gbk

$export Nls_lang

Edit the profile file for an Oracle user

2) Windows environment

Edit the Registry

Regedit.exe---"HKEY_LOCAL_MACHINE---" Software---"oracle-home

Or in the window settings:

Set Nls_lang=american_america. Zhs16gbk

Three

Viewing the DMP file character set

The DMP file exported with the Oracle Exp tool also contains character set information, and the 2nd and 3rd bytes of the DMP file record the character set of the DMP file. If the DMP file is small, such as only a few m or dozens of m, you can open it with UltraEdit (16 binary), look at the 2nd 3rd byte of content, such as 0354, and then use the following SQL to isolate its corresponding character set:

Sql> Select Nls_charset_name (To_number (' 0354 ', ' xxxx ')) from dual;

Zhs16gbk

If the DMP file is large, such as more than 2G (which is also the most common case), with a text editor opened very slowly or completely open, you can use the following command (on the UNIX host):

Cat Exp.dmp |od-x|head-1|awk ' {print $ |cut-c} ' 3-6

The corresponding character set can then be obtained using the SQL above.

Modifying the DMP file character set

The 2nd 3rd byte of the DMP file records the character set information, so directly modifying the contents of the 2nd 3rd byte of the dmp file can ' cheat ' the Oracle check. This is done theoretically only from subset to superset can be modified, but in many cases there is no subset and superset of the situation can also be modified, some of our commonly used character sets, such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK basic can be changed. Because the change is only the DMP file, so the impact is not small.

The specific modification method is more, the simplest is to modify the DMP file's 2nd and 3rd bytes directly with UltraEdit.

For example, to change the DMP file character set to ZHS16GBK, you can use the following SQL to isolate the character set corresponding to the 16 code: sql> Select To_char (nls_charset_id (' ZHS16GBK '), ' xxxx ') from Dual

0354

Then change the DMP file 2, 3 bytes to 0354.

If the DMP file is large, you cannot open it with your UE, you need to use the method of the program.

Oracle Database Character Set issues