Home » RDBMS Server » Server Utilities » Multilingual Support Parameters (Oracle 11g 11.2.0.1.0)
Multilingual Support Parameters [message #518473] Thu, 04 August 2011 01:31 Go to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Hey Everyone,

I had an dump file which contains multilingual data in the table but when I tried to do import in my database I got below mentioned error.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\dsharma>cd\

C:\>imp hell2/hell2 file='D:\SHARED\hell.dmp' full=y

Import: Release 11.2.0.1.0 - Production on Thu Aug 4 11:42:02 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


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

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HELL, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HELL's objects into HELL2
. . importing table                          "EMP"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "HELL2"."EMP"."ENAME" (actual: 14, maximum: 10)
Column 1 7499
Column 2 ALLéNALLéN
Column 3 SALESMAN
Column 4 7698
Column 5 20-FEB-1981:00:00:00
Column 6 1600
Column 7 300
Column 8 30
Column 9
Column 10 0
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "HELL2"."EMP"."ENAME" (actual: 14, maximum: 10)
Column 1 7566
Column 2 JONéSJONéS
Column 3 MANAGER
Column 4 7839
Column 5 02-APR-1981:00:00:00
Column 6 2975
Column 7
Column 8 20
Column 9
Column 10 0
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "HELL2"."EMP"."ENAME" (actual: 14, maximum: 10)
Column 1 7698
Column 2 BLAKéBLAKé
Column 3 MANAGER
Column 4 7839
Column 5 01-MAY-1981:00:00:00
Column 6 2850
Column 7
Column 8 30
Column 9
Column 10 0
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "HELL2"."EMP"."ENAME" (actual: 12, maximum: 10)
Column 1 7844
Column 2 TURNéRTURN
Column 3 SALESMAN
Column 4 7698
Column 5 08-SEP-1981:00:00:00
Column 6 1500
Column 7 0
Column 8 30
Column 9
Column 10 0
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "HELL2"."EMP"."ENAME" (actual: 14, maximum: 10)
Column 1 7900
Column 2 JAMéSJAMéS
Column 3 CLERK
Column 4 7698
Column 5 03-DEC-1981:00:00:00
Column 6 950
Column 7
Column 8 30
Column 9
Column 10 0
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "HELL2"."EMP"."ENAME" (actual: 12, maximum: 10)
Column 1 7934
Column 2 MILLéRMILL
Column 3 CLERK
Column 4 7782
Column 5 23-JAN-1982:00:00:00
Column 6 1300
Column 7
Column 8 10
Column 9
Column 10 0          8 rows imported
Import terminated successfully with warnings.
C:\>


My database is having the default characterset WE8MSWIN1252 and the value for "NLS_LENGTH_SEMANTICS" is "BYTE". I changed it to "CHAR" but nothing changed.

Thanks
Deepak

Report message to a moderator

Re: Multilingual Support Parameters [message #518479 is a reply to message #518473] Thu, 04 August 2011 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UTF8 is a varying-length character set which means that each character can take from 1 to 3 bytes, so your error.
You have to change all your varchar2 column length semantics from BYTE to CHAR using "ALTER TABLE MODIFY...".

The parameter NLS_LENGTH_SEMANTICS is effective for the future tables.

Regards
Michel

[Edit: add last sentence]

[Updated on: Thu, 04 August 2011 01:46]

Report message to a moderator

Re: Multilingual Support Parameters [message #518517 is a reply to message #518479] Thu, 04 August 2011 05:35 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
But I think columns need to be empty in order to change the data type of the columns.

Thanks
Deepak

Report message to a moderator

Re: Multilingual Support Parameters [message #518519 is a reply to message #518517] Thu, 04 August 2011 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not for this change, just test it.

Regards
Michel

Report message to a moderator

Re: Multilingual Support Parameters [message #518530 is a reply to message #518519] Thu, 04 August 2011 06:51 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
I have tried but i got the same error for the missing data at the time of import, the changes you said can be done only once the table import is done.

I want some which can be done at the import time.

Regards
Deepak

Report message to a moderator

Re: Multilingual Support Parameters [message #518534 is a reply to message #518530] Thu, 04 August 2011 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First import the table WITHOUT the rows, then change the type, then import the rows.
This is the ONLY way.

Regards
Michel

Report message to a moderator

Re: Multilingual Support Parameters [message #518592 is a reply to message #518534] Fri, 05 August 2011 00:12 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
If a table already exists and we use IGNORE=y , then rows are imported into existing tables without any errors but how to import table for the first time without rows.

Regards
Deepak

Report message to a moderator

Re: Multilingual Support Parameters [message #518609 is a reply to message #518592] Fri, 05 August 2011 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
imp ... rows=n

Regards
Michel

Report message to a moderator

Re: Multilingual Support Parameters [message #518612 is a reply to message #518609] Fri, 05 August 2011 01:38 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
I am facing this issue in the production database do you it will be good idea to tell them import without including rows and then change your data type and then again import.. Confused

Regards
Deepak

Report message to a moderator

Re: Multilingual Support Parameters [message #518614 is a reply to message #518612] Fri, 05 August 2011 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either they do that, either they are satisfied with some missing rows.

Regards
Michel

Report message to a moderator

Re: Multilingual Support Parameters [message #518616 is a reply to message #518614] Fri, 05 August 2011 01:47 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
But this won't be for the single table they might have to do for the several tables, as I can not ask them simply to change the CHARACTERSET of their database that will be like ending my career. Laughing

I'll try to implement it at my end for all the existing tables in database.

Thanks
Deepak

Report message to a moderator

Re: Multilingual Support Parameters [message #518619 is a reply to message #518616] Fri, 05 August 2011 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sometimes you have to swalow the pill even if you don't like it.
As I said, if you don't want to recreate the database, this is the only direct way to do it.

Regards
Michel

Report message to a moderator

Re: Multilingual Support Parameters [message #518672 is a reply to message #518619] Fri, 05 August 2011 07:17 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Hello Sir,

It didnot resolve the issue as when I tried to do theimport using the "rows=n" keyword,
all the tables were imported succesfully wihtout data and there after I made the changes
by altering the table with the command:

alter table xxx modify column_name varchar(250 char);



but there after when I tried to import the data I got the errors related to the foreign key
"violated - parent key not found"


Check out the Errors below:

ORA-02291: integrity constraint (SHARMA.FK_2A550F80) violated - parent key not found
Column 1 371
Column 2 9
Column 3 0
Column 4 214
Column 5 2
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SHARMA.FK_2A550F80) violated - parent key not found
Column 1 272
Column 2 5
Column 3 1
Column 4 150
Column 5 1
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SHARMA.FK_2A550F80) violated - parent key not found
Column 1 223
Column 2 44
Column 3 0
Column 4 127
Column 5 2
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SHARMA.FK_2A550F80) violated - parent key not found
Column 1 235
Column 2 3
Column 3 3
Column 4 133
Column 5 1
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SHARMA.FK_2A550F80) violated - parent key not found
Column 1 239
Column 2 8
Column 3 3
Column 4 135
Column 5 1
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SHARMA.FK_2A550F80) violated - parent key not found
Column 1 359


Thanks
Deepak

Report message to a moderator

Re: Multilingual Support Parameters [message #518677 is a reply to message #518672] Fri, 05 August 2011 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is another problem which refers to your data and we can't address not knowing them.
Try to do the import in a new empty schema and see what happens.
Also you could try to disable the constraints before the import and reenable them after.

Regards
Michel

Report message to a moderator

Re: Multilingual Support Parameters [message #518747 is a reply to message #518677] Sat, 06 August 2011 01:04 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member
why your not trying to CSSCAN utility before character set migration

Babu

Report message to a moderator

Re: Multilingual Support Parameters [message #518750 is a reply to message #518747] Sat, 06 August 2011 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no migration there, just an import, CSSCAN will give nothing.

Regards
Michel

Report message to a moderator

Re: Multilingual Support Parameters [message #518818 is a reply to message #518750] Sun, 07 August 2011 08:41 Go to previous message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Hi All,

The problem has been resolved, I first did the import without data bu using "ROWS=n", then I change the datatype of the column to CHAR by using "ALTER TABLE MODIFY COLUMN..." command.

Now before importing the data I disabled the constraint for the particular schema and there after issued the import command to import data and everything went well and the import was done successfully without any error.

Thanks everyone for all their efforts.

Regards
Deepak Smile

Report message to a moderator

Previous Topic: Questions on export
Next Topic: sqlloader
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 27 01:44:24 CST 2021
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.