Oralce prompt ORA-12899 error value too large for column when importing data

The following error occurred while exporting data from production to testing

Ora-02374:conversion Error loading table "Swtonline". Tbl_trans_log_history_b "
Ora-12899:value too large for column mer_addr_name (actual:54, maximum:40)

Preliminary estimates are caused by character set differences. Chinese occupies 3 bytes in UTF-8, ZHS16GBK occupies 2 bytes

To view the test library character set

The code is as follows

Copy Code

Sys@prod>select * from V$nls_parameters 2; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------- Nls_language Simplified Chinese Nls_territory Nls_currency￥ Nls_iso_currency Nls_numeric_characters., Nls_calendar Gregorian Nls_date_format DD-MON-RR Nls_date_language Simplified Chinese Nls_characterset Al32utf8 Nls_sort BINARY Nls_time_format HH.MI. Ssxff AM Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM Nls_time_tz_format HH.MI. Ssxff AM TZR Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR Nls_dual_currency￥ Nls_nchar_characterset AL16UTF16 Nls_comp BINARY Nls_length_semantics BYTE NLS_NCHAR_CONV_EXCP FALSE Rows selected. elapsed:00:00:00.00

View the production library character set

The code is as follows

Copy Code

Sys@oradb>select * from V$nls_parameters; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------- Nls_language American Nls_territory AMERICA Nls_currency $ Nls_iso_currency AMERICA Nls_numeric_characters., Nls_calendar Gregorian Nls_date_format DD-MON-RR Nls_date_language American Nls_characterset ZHS16GBK Nls_sort BINARY Nls_time_format HH.MI. Ssxff AM Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM Nls_time_tz_format HH.MI. Ssxff AM TZR Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR Nls_dual_currency $ Nls_nchar_characterset AL16UTF16 Nls_comp BINARY Nls_length_semantics BYTE NLS_NCHAR_CONV_EXCP FALSE Rows selected. elapsed:00:00:00.00

Change the character set of the test library as well as the production library

The code is as follows

Copy Code

Sys@prod>shutdown IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. Sys@prod>startup MOUNT ORACLE instance started. Total System Global area 939495424 bytes Fixed Size 2233960 bytes Variable Size 671091096 bytes Database buffers 260046848 bytes Redo buffers 6123520 bytes Database mounted. Sys@prod>alter SYSTEM ENABLE restricted session; System altered. elapsed:00:00:02.19 Sys@prod>alter SYSTEM SET job_queue_processes=0; System altered. elapsed:00:00:00.02 Sys@prod>alter SYSTEM SET aq_tm_processes=0; System altered. elapsed:00:00:00.01 Sys@prod>alter DATABASE OPEN; Database altered. elapsed:00:00:05.09 Sys@prod>alter DATABASE CHARACTER SET ZHS16GBK; ALTER DATABASE CHARACTER SET ZHS16GBK * ERROR at line 1: ORA-00911: Invalid character elapsed:00:00:00.00

---Report character set is incompatible, the character set superset is not checked with the Internal_use directive

The code is as follows	Copy Code
Sys@prod>alter DATABASE CHARACTER SET internal_use ZHS16GBK; Database altered. elapsed:00:00:53.35

Restart the database and import again to succeed.

The code is as follows	Copy Code
[Oracle@idata ~]$ IMPDP '/as Sysdba ' Directory=backup-dumpfile=cil_20130702.dmp logfile=cil_20130703.log table_exists_ Action=replace Schemas=epayment,logsdb,prepaid,risk,swtsettle,swtonline

Add the Oracle character encoding problem

Usually query Nls_characterset (database character set), Nls_nchar_characterset (national character set), should store multiple languages, need character set for UTF-8.

code is as follows	copy code
select * from v$ Nls_parameters WHERE PARAMETER in (' Nls_characterset ', ' nls_nchar_characterset ');

 
Results Utf-8,ok.
 
View Fielda varchar2 (in db)
 
View the problem db Fielda varchar2 (a)
 
(true before the problem occurred.) Did not notice that the two definitions are different ...
 
UTF-8 A Chinese character is 3 bytes, as can be seen from the above definition, if the char/byte definition results in a significant difference in the length of the stored data.
 
Sets the parameter nls_length_semantics you can specify the length by using byte (byte) or character (character) for the char or VARCHAR2 column when you create table.
 
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are character-based (character). The
 
Nls_length_semantics does not affect the SYS and system user tables, and the data dictionary definitions use bytes (byte).
 
can be displayed when defining a column by specifying the use of byte (byte) or character (character) to define the length:
 
CHAR  -regardless of nls_length_ Semantics is set to anything, using bytes (byte).
 
CHAR-characters (char) are used regardless of what nls_length_semantics is set to.