IMP-00016 Required Character Set Conversion Not Supported Error when Import to Oracle Database

When trying to import an Oracle database export, backup or dump, you may encounter the error IMP-00016 where the import terminate prematurely and not allow you to continue the import to the destination Oracle database. The error has the following message:

IMP-00016: required character set conversion (type 1 to 871) not supported
IMP-00000: Import terminated unsuccessfully

Note that depending on your export file and database character set, the “type 178 to 871″ may change from each environment. Other typical character set conversion not supported error include from type 178 to 871 or from type 31 to 871.

The cause for the Oracle error is because import utility could not convert the character format of the export file into the native character format which is the setting of the operating system client. In other world, the issue is due to the fact that there is conversion problem between the export dump file and the destination databases which have different character set value when Oracle import utility try to import the exported database by using the Unix’s NLS_LANG local environment variable value. If the operating system environment doesn’t show the value of NLS_LANG, the import (and also export) will be done in US7ASCII as the default value for NLS_LANG on UNIX platforms is AMERICAN_AMERICA.US7ASCII, regardless of the database characterset.

The resolution to resolve the IMP-00016 is to set the the NLS_LANG parameter in local OS env variable value to match the character set of the destination database and import the dump file. NLS_LANG can be change by using set or export command. For example:

$ export NLS_LANG=.WE8ISO8859P1

NLS_LANG is set in the registry on Windows platforms. For example, on an English Windows client, the code page is WE8MSWIN1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

You can check the character sets of the Oracle database in SQL*Plus by using following commands to list all NLS information:

SQL> col value format a25
SQL> col parameter format a25
SQL> select * from v$nls_parameters;

In the rows returned, NLS_CHARACTERSET will list the character set of the database. In ideal situation to avoid and minimize the potential errors, the recommended practise will be like the following:

At the system where database export is taken: Set NLS_LANG=. At the system where database import is done: Set NLS_LANG=.

Set the operating system NLS_LANG value to match the source or destination database character set will let Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so no validation or conversion is performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary. It’s more of an issue if source and destination databases have different character set, and source database contains special characters (for example chinese, japanese, spanish, german, special letters or other characters, which are not contained in US7ASCII), the target database will lose the original characters and show replacement characters instead. So, it’s best if the source and destination database has the same or similar character set.

To check the character set that a dump export and its database is using, check the export log, the characterset information should exists at the beginning of the log:

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

Similary, the import log will contain the information about what character set the import process is using and also for the target database, plus possible warning message, even if the import failed with IMP-00016 error, at the beginning of the import log:

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)

Share and contribute or get technical support and help at My Digital Life Forums.

Related Articles

• Oracle Database Import Error 3113/3114
• Oracle EXP-00091 Error When Export Database
• IMP-00013 Oracle Import Error
• Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error
• Oracle Database Link
• Change Oracle Database User Password
• How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error
• How to Escape Characters in Oracle PL/SQL Queries
• ORA-00942 Table or View Does Not Exist Oracle Error

This entry was posted on Friday, June 30th, 2006 at 11:54 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.