12

I'm trying to import an Oracle 11 export into Oracle 11 XE.

I get the following messages:

import in XE fehlerhaft import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)

Any ideas, how I can import this dump into Oracle 11 XE ?

Edit:

Given a table

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3)  NOT NULL,
    Name                  VARCHAR2(60) NOT NULL,
    Abkuerzung            VARCHAR2(5)  NOT NULL
);

I get errors like this

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "BDATA"."ARTIKEL"."ABKUERZUNG" (actual: 6, maximum: 5)
Column 1 ABL
Column 2 Aufbewahrungslösung
Column 3 AfbLö

Some rows are missing from the import.

4 Answers 4

8

If that is the actual DDL you are using to create the table, you could use the NLS_LENGTH_SEMANTICS parameter. If you set that to CHAR rather than the default of BYTE, a VARCHAR2(5) will be allocated enough space to store 5 characters in the database character set (potentially up to 20 bytes) rather than 5 bytes (which could allow just 1 character).

Unfortunately, changing the NLS_LENGTH_SEMANTICS probably won't be terribly helpful if you're relying on the import process to create the table-- the dump file will inherently add the CHAR or BYTE keyword so it would actually issue the statement

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL
);
3
  • I have the create table script and can modify it according to your proposal. If imp works when the tables are already created, all would be fine.
    – bernd_k
    May 16, 2011 at 14:58
  • @bernd_k - Cool. Then you can either set NLS_LENGTH_SEMANTICS before running the DDL or you could modify the DDL to add CHAR to every VARCHAR2 column declaration. When you do the import, you'll just have to tell it to ignore the failure of the CREATE TABLE statements since the tables will already exist. May 16, 2011 at 15:32
  • I changed my table definition ...VARCHAR2(60 CHAR) NOT NULL ... and used IMP with IGNORE=Y and Import terminated successfully with warnings.
    – bernd_k
    May 16, 2011 at 17:48
4

You don't have a choice of character set on XE so you cannot change it to suit the database you are trying to import. Would it be practical to migrate the source database before export?

The import should work, but character set conversion might mean some text columns with non-ascii characters won't look the same after the import. And rows can be rejected if they are too long in the new character set.

In your case, you are converting to UTF8, which will mean it is possible for a single byte character to grow during conversion to 2 (or more in theory). You may need to increase the column size before export or adjust the target schema and import the data in a separate step. See here for other possible data truncation problems

4
  • See my edit. My only hope is to first create the tables with extended width and than import the data ignoring the create tables from the import.
    – bernd_k
    May 16, 2011 at 13:09
  • are you using impdp? see here for how May 16, 2011 at 13:20
  • not yet, but perhaps a good time to learn.
    – bernd_k
    May 16, 2011 at 13:33
  • but note that impdp can only be used with exports created with expdp May 16, 2011 at 13:53
3

The Easiest way: (Shutdown neccesary):

First, Connect as sysdba:

sqplus / as sysdba

Next, execute the following script:

alter system set nls_length_semantics=CHAR scope=both;
shutdown;
startup restrict;
alter database character set INTERNAL_USE WE8ISO8859P1;
shutdown;
startup;

It worked for me in a Oracle 12c Standard Two Edition

Taken from: http://www.blogdelpibe.com/2015/05/como-solucionar-el-error-ora-12899.html

2
0

This worked for me. Instead of this:

imp u/p@db file=data.dmp

Try something like this in bash:

imp u/p@db file=<(perl -pe'/^CREATE TABLE/&&s/(VARCHAR2\(\d+)\)/$1 CHAR)/g' data.dmp)

This changes every col1 VARCHAR2(n) to col1 VARCHAR2(n CHAR) in lines starting with CREATE TABLE. You could also change data.dmp before running imp on it, if you're not able to <(...) in your shell for example:

perl -i.bk -pe'/^CREATE TABLE/&&s/(VARCHAR2\(\d+)\)/$1 CHAR)/g' data.dmp

...but it's not necessary in bash and something could go wrong in the conversion or in making the backup as stated by -i.bk.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.