Shulou(Shulou.com)06/01 Report--

The Oracle database character set involves three aspects:

Oracle server character set

Oracle client character set

Dmp file character set

First of all, you need to know that the most important parameter that affects the database character set is NLS_LANG

The format NLS_LANG = language_territory.charset consists of three parts, language, region, and character set

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

Territory: specifies the date and number format of the server

Charset: specify character set

Therefore, the character sets between the two databases can import and export data to each other as long as the third part is the same, and the previous effect is only on whether the message is in Chinese or English.

I.

View database server character set

Select * from nls_database_parameters

Modify the server character set (not recommended)

1. Shut down the database

SQL > SHUTDOWN IMMEDIATE

two。 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 go from parent set to 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 super 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. (keep in mind that the character set must be supported by ORACLE, otherwise you cannot start.) just follow the above method.

If a prompt such as' ORA-12717: Cannot ALTER DATABASE NATIONAL CHARACTER SET when NCLOB data exists' 'appears

There are two ways to solve this problem.

1. Modify the locale using the INTERNAL_USE keyword

two。 Use re-create, but re-create is a little 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 follow the above approach, there will be no problem with the locale of National charset

II.

View client character set

Select * from nls_instance_parameters

A setting that represents the character set of the client, 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 yourself in the dos window.

For example: set nls_lang=AMERICAN_AMERICA.ZHS16GBK

This only affects 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 character set on the server side is not consistent with the character set on the client side, please modify it to the same character set as the server side.

Modify the client character set:

1) UNIX environment

$NLS_LANG= "simplified chinese" _ china.zhs16gbk

$export NLS_LANG

Edit the profile file for the oracle user

2) Windows environment

Edit the registry

Regedit.exe-"HKEY_LOCAL_MACHINE -" SOFTWARE-"ORACLE-HOME

Or set it in the window:

Set nls_lang=AMERICAN_AMERICA.ZHS16GBK

3.

View dmp file character set

The dmp file exported with oracle's exp tool also contains the character set information, and the second and third bytes of the dmp file record the character set of the dmp file. If the dmp file is small, such as only a few megabytes or dozens of megabytes, you can open it with UltraEdit (hexadecimal), look at the contents of the second and third bytes, such as 0354, and then use the following SQL to find its corresponding character set:

SQL > select nls_charset_name (to_number ('0354)) from dual

ZHS16GBK

If the dmp file is large, such as more than 2G (which is also the most common case), and it is slow or impossible to open it with a text editor, you can use the following command (on the unix host):

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

Then using the above SQL, you can also get its corresponding character set.

Modify dmp file character set

The second and third bytes of the dmp file record the character set information, so directly modifying the contents of the second and third bytes of the dmp file can 'deceive' the oracle check. In theory, it can only be modified from subset to superset, but in many cases, it can also be modified without the relationship between subset and superset. Some commonly used character sets, such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK, can be changed. Because the change is only the dmp file, so it has little impact.

There are many specific modification methods, the simplest of which is to modify the second and third bytes of the dmp file directly with UltraEdit.

For example, if you want to change the character set of a dmp file to ZHS16GBK, you can use the following SQL to find the hexadecimal code corresponding to the character set: SQL > select to_char (nls_charset_id ('ZHS16GBK'),' xxxx') from dual

0354

Then change the 2 or 3 bytes of the dmp file to 0354.

If the dmp file is too large to be opened with ue, you need to use the program method.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.