Three types of Oracle Character Set

We all know that Oracle Character Set problems can be divided into three types: database character set, sqlplus character set (client Character Set), and terminal program character set (non-Oracle ), the following article describes three types of Oracle character sets.

1. Database Character Set

The character set of the database is the core of all character issues. Only when the character set of the database itself is correct can the character set of the client be correct. Only the clients here include sqlplus and our own database read applications.

When locating the problem, we need to first check what Oracle character set the database is currently.

1) query the database Character Set

 
 

  
  
select * from nls_database_parameters 
 
 

The nls_language indicates the display method, that is, the font of the sqlplus program, including simplified chinese and American america.

The nls_characterset is a character set. Common Oracle character sets include UTF8, US7ASCII, WE8ISO8859P1, ZHS16CGB231280, ZHS16GBK, and AL32UTF8.

2) modify the database Character Set

When we find that the database character set is incorrect, for example, we expect it to be GBK, and the database is currently other, leading to Chinese garbled characters. In this case, we need to modify the database character set. The procedure is as follows:

 
 

  
  
$sqlplus /nolog  
  
  
SQL>conn / as sysdba; 
 
 

If the database server has been started, run the shutdown immediate command to shut down the database server, and then run the following command:

 
 

  
  
SQL>STARTUP MOUNT;  
  
  
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;  
  
  
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;  
  
  
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;  
  
  
SQL>ALTER DATABASE OPEN;  
  
  
SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;  
  
  
SQL>ALTER DATABASE national CHARACTER SET ZHS16GBK;  
  
  
SQL>SHUTDOWN IMMEDIATE;  
  
  
SQL>STARTUP 
 
 

2 client Character Set

If the Oracle character set on the server side is correct, and sqlplus still cannot display Chinese characters correctly on the client side, this is generally because the character set on the server side is different from the character set on the client side. You only need to change the two to solve the problem.

1) query the client Character Set

 
 

  
  
elect * from nls_instance_parameters 
 
 

Client Character Set environment select * from nls_instance_parameters, which is from v $ parameter,

Indicates the character set setting of the client, which may be a parameter file, environment variable, or registry.

 
 

  
  
select userenv('language') from dual; 
 
 

Select * from nls_session_parameters in the session Character Set environment, which is derived from v $ nls_parameters, indicating the session's own settings, which may be the session environment variable or the session is completed by alter session. If the session has no special settings, it will be consistent with nls_instance_parameters.

The character set of the client must be the same as that of the server to correctly display non-Ascii characters of the database. If multiple settings exist, alter session> environment variable> registry> parameter file

The character set must be consistent, but the language settings can be different. We recommend that you use English for language settings. For example, if the Oracle character set is zhs16gbk, The nls_lang can be American_America.zhs16gbk. Or. zhs16gbk. Note that the point before zhs16gbk is required !!

2) modify the client Character Set

Oracle's sqlplus reads the nls_lang information in the OS environment variables.

NSL_LANG consists of three parts: Language _ region. Character Set): NLS_LANGUAGE, NLS_TERRITORY, and NLS_CHARACTERSET in the V $ NLS_PARAMETERS table.

For example, you can type

 
 

  
  
set nls_lang="Simplified chinese_china.utf8" 
  
  
set nls_lang="american_america.us7ascii" 
  
  
set nls_lang="american_america.zhs16gbk" 
  
  
set nls_lang="Simplified chinese_china.zhs16gbk" 
  
  
set nls_lang=".utf8" 
  
  
set nls_lang=".zhs16gbk" 
  
  
set nls_lang=".us7ascii" 
 
 

Unix is similar, but nls_lang should be capitalized NLS_LANG. Changing NLS_LANG in. profile or this. bash_profile (based on your shell) can maintain the environment variable value for a long time.

3. Terminal Character Set

If the database character set is consistent with the sqlplus Character Set and cannot be correctly displayed, it is likely that the Oracle Character Set of your terminal application is not supported. for example, if you use bash to log on to sqlplus, if your bash is a small character set, it cannot be displayed normally. in linux, to modify the bash character set, you can first enter locale to check which environment variables are available and then use export to set them.