Change oracle characterset 11g

Asked

Viewed 1,117 times

1

When performing a dump file import, oracle is returning me the following messages:
IMP-00019: rejected line due to ORACLE 12899 error
IMP-00003: Oracle error: 12899 found
ORA-12899: very large value for column "USUARIO"."TABELA"."COLUNA"(real: 41, max: 40)

The instruction used to import was:
imp system/senha file=arquivo.dmp log=imp.log fromuser=antigo touser=novo commit=y

From what I understand I must change the characterset of the instance of my database, but I have tried several times using the application CSSCANbut I can’t solve it at all.

Import instance configured as: characterset "AL32UTF8"
Export instance configured as: characterset "WE8MSWIN1252"

Someone has already been through this, can tell me how to convert the characterset of the cartoon instance?

sql oracle character-encoding pl-sql importing

2 answers

0

A great start to visualizing the problem of these specific errors is to use the oerr Utility is an excellent dictionary of oracle errors and their causes, in this book he talks about this error you are dealing with saying:

But, this error usually occurs when an attempt has been made to insert or update a column with a value that is too large for the width of the destination column. Column name is provided, together with the actual value width and the maximum permitted width of the column. Note that widths are reported.

To resolve you should examine the SQL statement to verify the fix. Check the data types of the source and destination column. Make the destination column wider or use a Substring in the source column.

0


You’re falling into a problem of NLS_LENGTH_SEMANTICS. The field is, I imagine, a VARCHAR2 length defined by number of bytes (Oracle default) and not by number of characters. For Oracle, a column VARCHAR2(40) is the same as VARCHAR2(40 BYTE).

A character in WE8MSWIN1252 is always defined by only one byte, but in AL32UTF8 there are Unicode characters defined by more than one byte in the case of accented text, for example. Thus, during conversion of charset command-driven imp, the data will occupy a larger number of bytes and no longer fit in your column.

To fix this problem once and for all you need to reset all fields to length in characters: VARCHAR2(40) should be amended to VARCHAR2(40 CHAR). Same goes for all CHAR type columns:

ALTER TABLE [tabela] MODIFY COLUMN [nome da coluna] VARCHAR2([tamanho] CHAR);
ALTER TABLE [tabela] MODIFY COLUMN [nome da coluna] CHAR([tamanho] CHAR);

You can do this in one of two circumstances:

  1. Change all columns in the source database. Thus, new exp will no longer cause this problem to imp subsequent.
  2. Generate a . sql file from its . dmp file and change the columns in the table definition itself. Use . sql changed to create only the tables in the destination instance and then do the imp to popular the newly created tables.

To generate an . sql from . dmp, use the parameter show=y of command imp, along with rows=n to obtain only the description of the data, without the records:

imp system/senha file=arquivo.dmp log=imp.log fromuser=antigo touser=novo show=y rows=n

Some references

Browser other questions tagged sql oracle character-encoding pl-sql importing

You are not signed in. Login or sign up in order to post.