Original Export / Import

How to Set Export Import Character Set

Garbled Text

Garbled data was found after importing a normal dump file. After doing some inspections, I found there’s NLS_LANG environment variable for user oracle in the source database server, but no NLS_LANG in the target server.

Default Character Set of Sessions

Since there’s no NLS_LANG to set NLS parameters at session-level, the import session will use the default character set US7ASCII to convert the dump file. That’s why we saw garbled text after import.

What is NLS_LANG?

What is NLS_LANG? NLS_LANG in the client’s environment is a notation that can notify database what NLS (National Language Support) parameters should be used for current session. The format of NLS_LANG is such:

<language>_<territory>.<charset>
In which, <language> is equivalent to NLS_LANGUAGE in the database.

Import without NLS_LANG Setting

Let’s see the log of the import job without setting NLS_LANG: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

As you can see, US7ASCII character set was used to convert the data.

Solution

The solution is easy, just set NLS_LANG environment variable for your OS account so as to match the character set of your dump file. For instance:

[oracle@ora12c ~] export NLS_LANG=AMERICAN_AMERICA.UTF8
[oracle@ora12c ~] echo $NLS_LANG
AMERICAN_AMERICA.UTF8
[oracle@ora12c ~] imp \"/ as sysdba\" parfile=SCHEMAS.par
...

Now we can continue our importing job. Please note that, we use “/ as sysdba” via OS authentication to perform the import job. We escaped the double quotes in the command line.

Import with NLS_LANG Setting

Let’s see the log of the import job with setting NLS_LANG: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

This time, UTF8 character set was used to import data.

For more globalization support on Oracle, please check Oracle 18c Database Globalization Support Guide: Setting Up a Globalization Support Environment.

As for NLS_LANG, you may check Oracle NLS_LANG FAQ. I believe most of your questions have answers there.

Leave a Reply

Your email address will not be published. Required fields are marked *