Oracle Character set import, export, transform

Import and export and conversion

Import and export is a commonly used data migration and conversion tool, because its export file is platform-independent, so in cross-platform migration, the most common.

In the export operation, it is very important that the client's character set settings, that is, the client's Nls_lang settings.

The Nls_lang parameter consists of the following parts:

Nls_lang=_.

Nls_lang the meanings of each part are as follows:

LANGUAGE specifies:

-oracle the language used for messages

-Date month and day display

Territory Specify

-Currency and number formats

-area and calculation of week and date habits

CHARACTERSET:

-Control the character set used by the client application

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

Or for a Unicode app set to UTF8

To view the code page for the current system on Windows, you can use the CHCP command:

E:\>chcp

Active code page: 936

Code page 936 is the Chinese character set GBK, at Microsoft's official site, we can be about 936 code page specific coding rules, please refer to the following links:

Http://www.microsoft.com/globaldev/...ce/dbcs/936.htm

Let's look at a simple test to see how these parameters work:

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

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect 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

----------

January-November-03

1 rows have 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 in disconnect

E:\>set Nls_lang=american_america. Zhs16gbk

E:\>sqlplus "/As SYSDBA"

Sql*plus:release 9.2.0.4.0-production on Sat 1 22:52:24 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

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-nov-03

1 row selected.

Sql>

To view client Nls_lang settings, you can use the following methods:

Windows uses:

Echo%nls_lang%

Such as:

E:\>echo%nls_lang%

American_america. Zhs16gbk

UNIX Uses:

Env|grep Nls_lang

Such as:

/opt/oracle>env|grep Nls_lang

Nls_lang=american_china. Zhs16gbk

Windows client settings, you can change the Nls_lang in the registry, the specific key value is located in:

Hkey_local_machine\software\oracle\homexx\

XX refers to the system number when there are multiple oracle_home.

Import and export are client-side products, as with Sql*plus and oralce forms, so using the Exp/imp tool converts the character set as defined by Nls_lang.

The character set used for the export will be recorded in the export file, and when the file is imported, the character set settings used when exporting will be checked, if this character set differs from the Nls_lang of the importing client

Settings, the character set will be converted according to the import client Nls_lang settings and, if necessary, further converted before the data is inserted into the database.

It is often preferable to set the client character set to the same as the database side when exporting, which avoids unnecessary data conversions on export and the same character set for the exported file as the database.

Even if you import the exported file into a database of different character sets in the future, you can defer the conversion to the import time.

When importing data, there are two main scenarios:

1. The source and target databases have the same character set settings

At this point, only the set Nls_lang equals the database character set can be imported (provided that the export uses the same character set as the source database, that is, the same as the three)

2. The source database and the target database have different character sets

If we use the same nls_lang as the source database when we export, we can set the character set that the client Nls_lang equals the export when importing, which

The sample conversion occurs only on the database side and only once.

For example:

If you are converting from we8mswin1252 to UTF8

1) Use Nls_lang=american_america. we8mswin1252 Export the database.

This creates an export file that contains we8mswin1252 data

2) use Nls_lang=american_america when importing. we8mswin1252

The conversion occurs only in the database where the insert data is UTF8.

The above assumptions can only be converted if the target database character set is a superset of the source database character set. If it is different, it is generally necessary to do some special processing.

Let's take a quick look at the import conversion process (taking oracle8i as an example):

1. Determine the export database character set environment

You can get the character set of the exported file by reading the export file header

2. Determine the character set of the import session, which is the NLS_LANG environment variable used by the import session

3.IMP Read Export File

Read the export file character set ID, and compare the Nls_lang of the import process

4. If the export file character set is the same as the import session character set, no conversion is required within this step

If it is different, you need to convert the data to the character set used by the import session.

However, this conversion can only occur between a single byte character set.

Let's look at a test:

E:\nls2>set Nls_lang=american_america. Us7ascii

Set import session Nls_lang to 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 the export client Nls_lang is also us7ascii

Import:release 8.1.7.1.1-production on Fri Nov 7 00:59:22 2003

(c) Copyright: Oracle Corporation. All rights reserved.

Connected to:oracle8i Enterprise Edition Release 8.1.7.1.1-production

With the partitioning option

Jserver Release 8.1.7.1.1-production

When importing, character set conversion is not required 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. Import for multiple byte character sets (for example: UTF8)

Need to set import session character set and export character set same

Otherwise 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 the 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

(c) Copyright: Oracle Corporation. All rights reserved.

Connected to:oracle8i Enterprise Edition Release 8.1.7.1.1-production

With the partitioning 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

In the process of setting from export file Us7ascii to import Nls_lang to ZHS16GBK, the single byte character set is not supported to convert multiple bytes, reporting the above error.

6. The import session character set should be the super of the export character set, otherwise, the exclusive characters will be difficult to convert correctly.

7. When the data is converted to the import session character set setting, if the import session character set differs from the Import database character set, then the last step is required, which requires importing the database characters

Set is the super of importing the session character set, otherwise some proprietary characters will not be converted properly.

We continue to look at the two processes above, and here are two principles:

1. If the Nls_lang setting is the same as the database, then the data (2 binaries in transit, of course) is inserted directly into the database without conversion.

2. If the Nls_lang setting differs from the database, the data needs to be converted before it can be inserted into the database.

Let's look back at the first example above:

:

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 step of the converted data, us7ascii to ZHS16GBK lost first, as is inserted into the database, we see the database is stored in the wrong character (in the back

Section we did a detailed conversion):

E:\nls2>sqlplus Rainny/rainny

Sql*plus:release 9.2.0.4.0-production on Fri Nov 7 00:35:39 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.1-production

With the partitioning option

Jserver Release 8.1.7.1.1-production

Sql> select * from test;

NAME

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

2bJT

Test

__________________