CODEWITHSUNDEEP
oracle-databaseimportoracle11gexport
user3318414
user3318414

Reputation: 1

Oracle export import issue

I would like ask you for help in area of data export/import between databases with different character sets. Database from which we are exporting is:

WE8ISO8859P1 / NCHAR AL16UTF16

Database to import is:

UTF8 / UTF8

I tried to use different NLS_LANG settings but without success (see below). I am not getting any error during import only comments like:

imp test/test@test file=mig_test.expdat log=imp.log

Import: Release 10.2.0.1.0 - Production on Thu May 22 08:54:36 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)

Nothing is imported. I appreciate your comment.

Upvotes: 0

Views: 8951

Answers (1)

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

Step 1 : export

Export with the NLS_LANG set to AMERICAN_AMERICA.WE8ISO8859P1 . This will create an export file with WE8ISO8859P1 data.

Step 2: import

Import with the NLS_LANG set to AMERICAN_AMERICA.WE8MSWIN1252 WE8MSWIN1252 codes are inserted into the database, the conversion to UTF8 is done while inserting the data in the UTF8 database.

OR

Import with the NLS_LANG set to AMERICAN_AMERICA.UTF8 The conversion to UTF8 is done by the import tool and UTF8 codes are inserted into the database

Slightly off-topic 1

You are using the old import and export tools. You really should use datapump (impdp, expdp). It's a newer, faster and more flexible alternative than the old import/export tool. http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

Slightly off-topic 2

Do not use the character set named UTF8 as the database character set unless required for compatibility with Oracle Database clients and servers in version 8.1.7 and earlier, or unless explicitly requested by your application vendor. Despite having a very similar name, UTF8 is not a proper implementation of the Unicode encoding UTF-8. If the UTF8 character set is used where UTF-8 processing is expected, data loss and security issues may occur. This is especially true for Web related data, such as XML and URL addresses.

Oracle recommends AL32UTF8 as the database character set. AL32UTF8 is Oracle's name for the UTF-8 encoding of the Unicode standard.

Upvotes: 1

Related Questions