Full description of Oracle Character Set

Oracle Character Set problems are often encountered when learning Oracle. Here we will introduce solutions to Oracle Character Set problems. The Oracle character set is a collection of symbols for the interpretation of byte data. It can be divided into different sizes and have an inclusive relationship. Oracle supports the national language architecture, allowing you to store, process, and retrieve data in a localized language. It makes database tools, error messages, sorting order, date, time, currency, numbers, and calendar automatically adapt to localization languages and platforms.

 
 

  
  
SELECT * FROM V$NLS_PARAMETERS  
  
  
NLS_LANGUAGE SIMPLIFIED CHINESE  
  
  
NLS_TERRITORY        CHINA  
  
  
NLS_CURRENCY RMB  
  
  
NLS_ISO_CURRENCY CHINA  
  
  
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       RMB  
  
  
NLS_NCHAR_CHARACTERSET     UTF8  
  
  
NLS_COMP        BINARY  
  
  
NLS_LENGTH_SEMANTICS   BYTE  
  
  
NLS_NCHAR_CONV_EXCP   FALSE 
 
 

How to query the Oracle Character Set

Oracle has three character sets: one is the character set on the El server side, the other is the character set on the Oracle client side, and the other is the dmp file character set. During data import, the three character sets must be consistent before the data can be correctly imported.

1. view the character set of the kernel El server.

 
 

  
  
select userenv('language') from dual;  
  
  
SIMPLIFIED CHINESE_CHINA.AL32UTF8 
 
 

2. view the dmp file Character Set

The dmp file exported using Oracle's exp tool also contains character set information. The 2nd and 3rd bytes of the dmp file record the character set of the dmp file. If the dmp file is not large, for example, only a few MB or dozens of MB, you can use UltraEdit to open it (in hexadecimal mode) and view the content of 2nd 3rd bytes, such as 0354, then, use the following SQL statement to find the corresponding character set:

 
 

  
  
select nls_charset_name(to_number('0354','xxxx')) from dual 
 
 

3. view the character set of the Oracle client

Is the NLS_LANG of the corresponding OracleHome in the registry. You can also set it in the dos window. For example, set nls_lang = AMERICAN_AMERICA.ZHS16GBK only affects the environment variables in the window. The Oracle character set is described above.

1. Analysis and Discussion on Oracle Database Restoration
2. Oracle remote disk image
3. New Oracle 11g function display
4. Detailed analysis of Oracle XML data
5. Oracle Data Guard