Oracle character set modified AL32UTF8 to ZHS16GBK

Time:2020-8-27

In the process of using Oracle, there will be all kinds of problems and errors. Among them, ora-12899 is the problem that occurred when I imported the data to my local machine some time ago. Fortunately, this problem has been solved. Now let’s share the solution;
Ora-12899 is caused by the character set. Chinese takes 3 bytes in UTF-8 and 2 bytes in ZHS16GBK. The character set of the source DMP file is the data inverted from the ZHS16GBK library. Now it is imported into the library whose target character set is UTF-8, so ora-12899 will appear
In fact, as long as you modify the Oracle character set, you can solve this problem very well;

Microsoft Windows [version 10.0.18362.10024]
(c) 2019 Microsoft Corporation。 All rights reserved.

C:\Users\asus>sqlplus /nolog

SQL * Plus: release 18.0.0.0.0 - production on Thursday March 12 14:34:38 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select userenv('language') from dual;
 
USERENV('LANGUAGE')

 
SIMPLIFIED CHINESE_CHINA.AL32UTF8
 
SQL> shutdown immediate
The database has been shut down.
Database has been dismounted.
The Oracle routine has been closed.
SQL> STARTUP MOUNT EXCLUSIVE;
Oracle routine started.
 
Total System Global Area 2533359616 bytes
Fixed Size                  3835256 bytes
Variable Size             738200200 bytes
Database Buffers         1778384896 bytes
Redo Buffers               12939264 bytes
Finished loading database.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
 
The system has changed.
 
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
 
The system has changed.
 
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
 
The system has changed.
 
SQL> ALTER DATABASE OPEN;
 
The database has changed.
 
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
 
The database has changed.
 
SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
 
The database has changed.
 
SQL> SHUTDOWN IMMEDIATE
The database has been shut down.
Database has been dismounted.
The Oracle routine has been closed.
SQL> STARTUP
Oracle routine started.
 
Total System Global Area 2533359616 bytes
Fixed Size                  3835256 bytes
Variable Size             738200200 bytes
Database Buffers         1778384896 bytes
Redo Buffers               12939264 bytes
Finished loading database.
The database is already open.
SQL> select userenv('language') from dual;
 
USERENV('LANGUAGE')

SIMPLIFIED CHINESE_CHINA.ZHS16GBK
 
SQL> col parameter format a32;
SQL> col value format a32;
SQL> select * from nls_database_parameters;
 
PARAMETER                        VALUE

NLS_RDBMS_VERSION                12.1.0.2.0
NLS_NCHAR_CONV_EXCP              FALSE
NLS_LENGTH_SEMANTICS             BYTE
NLS_COMP                         BINARY
NLS_DUAL_CURRENCY                $
NLS_TIMESTAMP_TZ_FORMAT          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT                  HH.MI.SSXFF AM
NLS_SORT                         BINARY
NLS_DATE_LANGUAGE                AMERICAN
 
PARAMETER                        VALUE

NLS_DATE_FORMAT                  DD-MON-RR
NLS_CALENDAR                     GREGORIAN
NLS_NUMERIC_CHARACTERS           .,
NLS_NCHAR_CHARACTERSET           AL16UTF16
NLS_CHARACTERSET                 ZHS16GBK
NLS_ISO_CURRENCY                 AMERICA
NLS_CURRENCY                     $
NLS_TERRITORY                    AMERICA
NLS_LANGUAGE                     AMERICAN
 
20 rows selected.

 

Here we change the character set