Preliminary discussion of character sets (four)

xiaoxiao2021-03-06 51

LINK:

http://www.eygle.com/special/nls_character_set_04.htm

4. Import export and conversion

The import export is a data migration and conversion tool we use, because its export file has a platform-independent, so in cross-platform migration, most commonly used. At the time of export operation, it is very important that the client's character set setting is also the NLS_LANG settings of the client. The NLS_LANG parameter consists of the following parts:

NLS_LANG = _ .

NLS_LANG has the following meanings as follows:

Language Specifies:

-Orcle message

- Date Month and Day Show

Territory designated

- Currency and digital format

- Regional and computational habits

Characterset:

- Control the character set used by the client application

Usually set or equal to the client (such as a Windows) code page

Or set to UTF8 for Unicode applications

View the current system code page on Windows You can use the chcp command:

E: /> CHCP

Activities Page: 936

The code page 936 is also the Chinese character set GBK, on the official website of Microsoft, we can be subject to specific coding rules for the 936 code page, please refer to the following link:

http://www.microsoft.com/globaldev/reference/dbcs/936.htm

Let's take a simple test to learn about the role of these parameters:

E: /> SET NLS_LANG = Simplified Chinese_China.zHS16GBK

E: /> SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.4.0 - Production ON Saturday November 1 22:51:59 2003

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the partitioning, Oracle Label Security, OLAP AND ORACLE DATA MINING OPTIONS

JServer Release 9.2.0.4.0 - Production

SQL> SELECT SYSDATE from DUAL;

Sysdate

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

01- November -03

1 line has been selected.

SQL> EXIT

From Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the partitioning, Oracle Label Security, OLAP AND ORACLE DATA MINING OPTIONS

JServer Release 9.2.0.4.0 - Production interrupt

E: /> SET NLS_LANG = American_america.zHS16GBK

E: /> SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.4.0 - Production on Sat Nov 1 22:52:24 2003

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the partitioning, Oracle Label Security, OLAP AND ORACLE DATA MINING OPTIONSJSERVER RELEASE 9.2.0.4.0 - PRODUCTION

SQL> SELECT SYSDATE from DUAL;

Sysdate

---------

01-NOV-03

1 row selected.

SQL>

View client NLS_LANG settings You can use the following methods:

Windows use:

ECHO% NLS_LANG%

Such as:

E: /> ECHO% NLS_LANG%

American_america.zhs16GBK

UNIX use:

ENV | GREP NLS_LANG

Such as:

/ OPT / ORACLE> ENV | GREP NLS_LANG

NLS_LANG = American_China.zhs16GBK

Windows client settings, you can change NLS_LANG in the registry, and the specific key value is:

HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE / HOMEXX /

XX refers to the system number when there is a plurality of Oracle_home.

Import and export are the client products, like SQL * Plus and Oralce Forms, so use the EXP / IMP tool to convert the character set in a manner defined by NLS_LANG.

The character set for exporting will be recorded in the export file. When the file is imported, the character set setting used when the export is checked. If this character set is different from the NLS_LANG settings imported, the character set will set according to the import client NLS_LANG setting. Conversion, if necessary, further conversion is performed before the data is inserted into the database.

It is usually the same when exporting the client character set is set to the database, which avoids unnecessary data conversion when exporting, exporting files and databases have the same character set. Even in the future, the export file will be imported into the database of different character sets, the conversion can also be delayed to the import time.

When data is imported, there are two cases: 1. Source database and target database have the same character set setting, only need to set NLS_LANG is equivalent to the database character set (provided is that the export is the source database The same character set, that is, the same three

2. Source database and target database character set Different if the NLS_LANG used when we export is the same character set as the source database, you can set the client nls_lang equal to the character set used when the export is exported, so that the conversion only occurs at the database end And only once.

For example, if converted from WE8MSWIN1252 to UTF8 1) Export the database using NLS_LANG = American_America.We8MSWIN1252. The export file created at this time contains the data of We8MSWIN1252 2) When imported, use NLS_LANG = American_America.We8mswin1252 This time the conversion only occurs in the database of ITF8.

The above assumptions can only be converted only when the target database character set is a supercoming source database character set. If different, some special processes are generally required.

Let's take a look at the imported conversion process (take Oracle8i as an example):

1. Determine the Export Database Chart Environment By reading the exported file header, you can get the character set setting of the export file 2. Determine the character set imported into the session, that is, the NLS_LANG environment variable used by the session 3.Imp read export file read out The file character set id, and the NLS_LANG imported process is compared 4. If the export character set is exported, if the session character set is the same, if the conversion is required in this step, it is necessary to convert the data to the character used by the session. set. However, this conversion can only be done between single Byte character sets. Let's take a test:

E: / NLS2> SET NLS_LANG = American_america.us7ascii Settings Import Session NLS_LANG for US7ASCII

E: / NLS2> E: / Oracle / ORA8I / BIN / IMP EYGLE / EYGLE FILE = SUS7ASCII- CUS7ASCII-Exp817.dmp fromuser = Eygle Touser = Eygle Tables = TEST

This export file is exported from the US7ASCII database and exports the client NLS_LANG is also US7ASCII.

Import: Release 8.1.7.1.1 - Production on Fri Nov 7 00:59:22 2003

Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production

WITH THE PartInog Option

JServer Release 8.1.7.1.1 - Production

At this time, you don't need to perform character set conversion between DMP files and NLS_LANG.

Export File Created by Export: V08.01.07 Via Conventional Path

Import Done in US7ASCII Character Set and zhs16GBK nchar Character Set

Import Server Uses ZHS16GBK Character Set (Possible Charset Conversion)

Export Server Uses Utf8 Nchar Character Set (Possible Ncharset Conversion)

Importing Table "Test" 2 ROWS IMPORTED

Import Terminated SuccessFully without Warnings.

5. For multi-Byte character sets (such as UTF8) require settings to import the Session character set and export the character set, you will encounter: IMP-16 "Required Character Set Conversion (Type% Lu TO% Lu) Not supported error. :

E: / NLS2> SET NLS_LANG = American_america.zHS16GBK

Import session character set to zHS16GBK

Import US7ASCII export file

E: / NLS2> E: / Oracle / ORA8I / BIN / IMP EYGLE / EYGLE FILE = SUS7ASCII- CUS7ASCII-Exp817.dmp fromuser = Eygle Touser = Eygle

Import: Release 8.1.7.1.1 - Production On Fri Nov 7 00:38:55 2003

Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production

WITH THE PartInog Option

JServer Release 8.1.7.1.1 - Production

IMP-00016: Required Character Set Conversion (Type 1 To 852) Not supported

IMP-00000: Import Terminated UnsuccessFully During the import file US7ASCII to the import NLS_LANG is set to zHS16GBK, the single Byte character set is not supported by multiple BYTE conversions, and the above error is reported.

6. Importing the session character set should be super, otherwise, otherwise the proprietary characters will be difficult to convert. 7. After the data is converted to import the session character set setting, if the import session character set is different from the import database character set, the last step is required, which requires the import database character set to import the session character set, otherwise certain The proprietary character will not be converted normally. We continue to look at the two processes in the above, there are two principles here: 1. If the setting of NLS_LANG is the same, the data (which is of course 2 in the transfer process) is directly inserted into the database without the conversion. 2. If the settings of NLS_LANG are different, the data needs to be converted to the database after the data needs to be converted. Let's look back in the first example ::

Export File Created by Export: V08.01.07 Via Conventional Path

Import Done in US7ASCII Character Set and zhs16GBK nchar Character Set

Import Server Uses ZHS16GBK Character Set (Possible Charset Conversion)

Export Server Uses Utf8 Nchar Character Set (Possible Ncharset Conversion)

Importing Table "Test" 2 ROWS IMPORTED

Import Terminated SuccessFully without Warnings.

At this time, after the first conversion data, US7ASCII to zHS16GBK lost the first place, inserted into the database, we see that the database is stored in the database (in the back section we have made detailed conversion):

E: / NLS2> SQLPlus Eygle / Eygle

SQL * Plus: Release 9.2.0.4.0 - Production on Fri Nov 7 00:35:39 2003

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production

WITH THE PartInog Option

JServer Release 8.1.7.1.1 - Production

SQL> SELECT *.

Name

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

2bjt

Test

In Oracle9i, the above situation is slightly different.

转载请注明原文地址:https://www.9cbs.com/read-116196.html

9cbs

New Post(0)