Home » RDBMS Server » Server Utilities » ORA-12899 in import (Oracle 10.2.0.4 AIX 6L )
ORA-12899 in import [message #486165] Tue, 14 December 2010 01:46 Go to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hello Export,

ORA 12899 prompts when we are doing import from 10.2.0.3 -> 11g R2.
Also it is cross platform, AIX -> windows 2003.

Please suggest for corrective action.

Regards,
JAY VARDHAN
Re: ORA-12899 in import [message #486171 is a reply to message #486165] Tue, 14 December 2010 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
12899, 00000, "value too large for column %s (actual: %s, maximum: %s)"
// *Cause: An attempt was made to insert or update a column with a value
//         which is too wide for the width of the destination column.
//         The name of the column is given, along with the actual width
//         of the value, and the maximum allowed width of the column.
//         Note that widths are reported in characters if character length
//         semantics are in effect for the column, otherwise widths are
//         reported in bytes.
// *Action: Examine the SQL statement for correctness.  Check source
//          and destination column data types.
//          Either make the destination column wider, or use a subset
//          of the source column (i.e. use substring).


Same character set?
Post both NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET values from nls_database_parameters in both databases.

Regards
Michel

[Updated on: Tue, 14 December 2010 02:06]

Report message to a moderator

Re: ORA-12899 in import [message #486174 is a reply to message #486171] Tue, 14 December 2010 02:17 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hello Michel,

Thanks for prompt response. Please find the required value.

Source database:

NLS_NCHAR_CHARACTERSET -> AL16UTF16
NLS_CHARACTERSET ->WE8ISO8859P1

Destination database:


NLS_CHARACTERSET ->AL32UTF8
NLS_NCHAR_CHARACTERSET ->AL16UTF16

Regards,
Jay vardhan
Re: ORA-12899 in import [message #486177 is a reply to message #486174] Tue, 14 December 2010 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WE8ISO8859P1 single byte characters.
AL32UTF8 multibyte characters from 1 to 4 bytes per character.
This is the reason of your error.
First import the tables without the rows, then change every definition of (VAR)CHAR from BYTE length semantics to CHAR length semantics, then import the data.

Example:
ALTER TABLE x MODIFY (col VARCHAR2(n CHAR));

Also change "nls_length_semantics" instance parameter to CHAR.

Regards
Michel
Re: ORA-12899 in import [message #486183 is a reply to message #486177] Tue, 14 December 2010 03:11 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks for the suggestion & explanation of NLS parameter.

Actually what we are doing is recovering the schema, so not known the exact size of tables. So now what would i set the values of N in command "ALTER TABLE x MODIFY (col VARCHAR2(n CHAR));" . Please let me know if have any recommended value.

Regards,
Jay vardhan
Re: ORA-12899 in import [message #486190 is a reply to message #486183] Tue, 14 December 2010 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"N" value is the current value, no need to change it, just add the "CHAR" option.

Regards
Michel
Re: ORA-12899 in import [message #486195 is a reply to message #486190] Tue, 14 December 2010 04:21 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks Michel.

I'm working on that and update you as completed successfully.

Once again thanks for support.

Regards,
Jay vardhan
Re: ORA-12899 in import [message #486944 is a reply to message #486195] Tue, 21 December 2010 06:49 Go to previous message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Sorry Michel for late response.

We have imported the data successfully.

Thanks for the support.

Regards,
Jay vardhan.
Previous Topic: ora-01410 in export
Next Topic: sql*loader
Goto Forum:
  


Current Time: Sun Sep 19 21:49:27 CDT 2021