Change the characterset of oracle 11g

Question

Change the characterset of oracle 11g

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

When performing a dump file import, oracle is returning the following messages:
IMP-00019: Line Rejected Due to Error 12899 ORACLE
IMP-00003: Oracle Error: 12899 found
ORA-12899: too large value for column "USUARIO"."TABELA"."COLUNA" (real: 41, maximum: 40)

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

So I understand I should change the characterset of the instance of my database, but I have tried several times using the CSSCAN application but I can not solve it at all.

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

Someone has already gone through this, can you tell me how I can convert the characterset of the bd instance?

sql oracle character-encoding pl-sql

asked by anonymous 22.05.2017 / 19:52

2 answers

Identify an optgroup with jQuery Activity Bugando + Memory error

score 0 · Answer 1

A great start to seeing the problem of these specific errors is to use the oerr utility is an excellent error dictionary of oracle and its causes, in this book he talks about this error that you are dealing 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 to the width of the target column. The column name is provided, together with the actual width of the value and the maximum allowable width of the column. Notice that the widths are reported.

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

score 0 · Answer 2

You're falling into a problem with NLS_LENGTH_SEMANTICS . The field is, I suppose, a VARCHAR2 length defined by number of bytes (the Oracle default) and not by number of characters. For Oracle, a VARCHAR2(40) column 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 case of accented text, for example. So, during the charset conversion done by the imp command, the data will occupy a larger number of bytes and will 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 changed to VARCHAR2(40 CHAR) . The same goes for all columns of type CHAR:

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:

Change all the columns in the source database. Therefore, new exp will no longer cause this problem for imp subsequent.

Generate a .sql file from your .dmp file and change the columns in the table definition itself. Use changed .sql to create only the tables in the target instance and then make imp to popular the newly created tables.

To generate a .sql from .dmp, use the show=y parameter of the imp command, along with rows=n to get 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