Home » RDBMS Server » Server Utilities » imp Issue (Oracle,9.2.0.6.0,HP-UX )
imp Issue [message #490638] Wed, 26 January 2011 05:24 Go to next message
sysanees
Messages: 3
Registered: January 2011
Location: India
Junior Member
HI,

I am getting the below error when I import a table from Prod to Dev. I understand this error will be occured if length of the datatype is low.
But in this case, First I got the error when the datatype(length) which is 25 for the column PASSWORD column.Then I increased the length of this column to 45, then it was imported successfully.

Pls let me know why am facing the error when the datatye and length for this table is same in prod and dev?

What are the possible ways to import the data without increasing the PASSWORD column length?

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "ANEES"."SALSA_WEB_ACCESS"."PASSWORD" (actual: 28, maximum: 25)

PROD DB
=======

DB Version : 9.2.0.6.0
Exp/IMP version : 9.2.0.6.0
NLS_CHARACTERSET : US7ASCII

SQL> desc salsa_web_access
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(25)
 PASSWORD                                  NOT NULL VARCHAR2(25)
 EMAIL                                              VARCHAR2(255)
 CONTACT_TEL_NO                                     VARCHAR2(22)


DEV DB
======

DB Version : 10.2.0.4.0
Exp/IMP version : 10.2.0.1.0
NLS_CHARACTERSET : AL32UTF8

SQL> desc salsa_web_access
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(25)
 PASSWORD                                  NOT NULL VARCHAR2(45)
 EMAIL                                              VARCHAR2(255)
 CONTACT_TEL_NO                                     VARCHAR2(22)

[Updated on: Wed, 26 January 2011 05:26]

Report message to a moderator

Re: imp Issue [message #490640 is a reply to message #490638] Wed, 26 January 2011 05:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got multi-byte characters.
Why does dev have a different characterset to prod?
That's just asking for trouble.
Re: imp Issue [message #490642 is a reply to message #490638] Wed, 26 January 2011 05:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The issue is, charactersets are different in PROD and Dev.
Re: imp Issue [message #490647 is a reply to message #490642] Wed, 26 January 2011 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This error should NOT happen between the 2 character sets you mentioned UNLESS you have non US7ASCII character in your source database.

Regards
Michel
Re: imp Issue [message #490651 is a reply to message #490647] Wed, 26 January 2011 06:42 Go to previous messageGo to next message
sysanees
Messages: 3
Registered: January 2011
Location: India
Junior Member
So, it is impossible to avoid this error when the characterset is different for the 2 databases.
But the other tables resides in PROD database are importing to DEV successfully without any issue. Then why am facing this issue only for this particular table alone?

[Updated on: Wed, 26 January 2011 06:43]

Report message to a moderator

Re: imp Issue [message #490666 is a reply to message #490651] Wed, 26 January 2011 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So, it is impossible to avoid this error when the characterset is different for the 2 databases.

No it is possible above all when the data are correct regarding the character set you use.

Regards
Michel
Re: imp Issue [message #490669 is a reply to message #490666] Wed, 26 January 2011 09:00 Go to previous messageGo to next message
sysanees
Messages: 3
Registered: January 2011
Location: India
Junior Member
I can't get you.
From your answer I got that, if the characterset is same with the database characterset for the particular data, then we can avoid this error.
In this case, how can I find out the characterset of my table data?

But as for as my understanding about oracle, whatever the data in the database will be in our database characterset. Pls correct me if I am wrong.
Re: imp Issue [message #490676 is a reply to message #490669] Wed, 26 January 2011 10:32 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The situation is simple:
1/ You have no problem to import correct data from US7ASCII to AL32UTF8 database
2/ YOU, you have a problem because you inserted incorrect data in your database

The solution is also simple:
1/ Fix you data
2/ export/import.

Regards
Michel
Previous Topic: problem with export illegal parameter value in OCI lob function
Next Topic: ?Trivial? sqlldr problem
Goto Forum:
  


Current Time: Fri Mar 29 06:52:08 CDT 2024