Solution: Oracle cannot display Chinese Characters in RedHat Enterprise Linux

I. Environment Description:
Original Server 192.168.1.110 Windows Server 2003 system character set: ZHS16GBK
The new ECS 192.168.1.120 RedHat Enterprise Linux system character set is WE8ISO8859PI.

Ii. Problem description:
1.110 The database on the server is a Chinese character set, and the values of multiple columns in the data table are Chinese.
The character set in the database on the 1.120 server does not support ZHS16GBK. Therefore, when importing the. dmp file exported from 1.110 to the database, the following error occurs? Replace Chinese characters.

Iii. Solution description:
1. Log On As A root user and set the linux system language to Chinese, that is, zh_CN (which can be modified in the/etc/sysconfig/i18n file, provided that you have an installation package in Chinese ); after the settings are complete, restart (Remote logon seems to have to be restarted) or press ctrl + alt + backspace to make the language settings take effect.

The reason why we set the system language to Chinese is that we can display Chinese characters when logging on to the server using a client (such as secureCRT) to verify that Oracle is correct.

2. log on to the. bash_profile file under the Editing home directory as an oracle user and add export NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
Nls_lang is the character set variable of the database. The default value is AMERICAN.

3. perform the following steps to modify the character set of a database using sysdba:

SQL> conn/as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter session set SQL _TRACE = TRUE; // statement tracking
System altered.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
 
System altered.
 
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;
 
System altered.
 
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0;
 
System altered.
 
SQL> alter database open;

Database altered.
 
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
Alter database character set ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
 
 
Prompt our character set: the new character set must be the superset of the old character set. In this case, we can skip the superset check and make changes at www.bkjia.com:
 
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
 
Database altered.

Check whether the character set has changed:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
 
Database altered.
 
SQL> select * from v $ nls_parameters;
 
PARAMETER VALUE
-------------------------------------------------------------------------------
NLS_LANGUAGE ZHS16GBK -- changed, previously 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
 
PARAMETER VALUE
-------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH. MI
NLS_TIME_TZ_FORMAT HH. MI. SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH. MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
 
19 rows selected.

Restart refresh and check again:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL> select * from v $ nls_parameters;
 
PARAMETER VALUE
-------------------------------------------------------------------------------
NLS_LANGUAGE ZHS16GBK
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
 
PARAMETER VALUE
-------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH. MI
NLS_TIME_TZ_FORMAT HH. MI. SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH. MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
 
19 rows selected.

4. Finally, import the. dmp file.
$ Imp test/test1234
____________________________________________________________________________________________ Prompt information for Import
Import file: expdat. dmp> mydata. dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT: V11.02.00 via conventional path
Import done in US7ASCII character set and AL16UTF16 NCHAR character set
Import server uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no>

Ignore create error due to object existence (yes/no): no>

Import grants (yes/no): yes>

Import table data (yes/no): yes>

Import entire export file (yes/no): no> yes
-------------------------------------------------

After the preceding steps are completed, if garbled characters appear after the select statement is run on the 1.120 server (which of the following code has already appeared before? There is a big difference, the reason for garbled characters is because the terminal character set is the default UTF-8, we can change the character set of terminal in linux to GBK login to try to see if there is garbled ), at this time, you can also use the client tool secureCRT to log on to the 192.168.1.120 server as an oracle user, and set the character encoding of the client to simplified Chinese. Then, you can view the select statement and the display is Chinese. This means that you can use a client tool to insert data into a data table.