View and modify the Oracle database character set

1. Querying the character set of the Oracle server side

Sql> select userenv (' language ')  from dual; USERENV (' LANGUAGE ')----------------------------------------------------American_america. zhs16gbksql>  or Sql> set line 150sql> col parameter for a30sql > col value for a30sql> select * from nls_database_ Parametersparameter       value------------------------------ -------- ----------------------NLS_RDBMS_VERSION       12.1.0.2.0NLS_NCHAR_CONV_EXCP        FALSENLS_LENGTH_SEMANTICS        bytenls_comp       binarynls_dual_currency         $NLS _TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI. Ssxff am tzrnls_time_tz_format       hh.mi. ssxff Am tzrnls_timestamp_format       dd-mon-rr hh.mi. Ssxff amnls_time_format        hh.mi. ssxff amnls_sort       binarynls_date_language        AMERICANPARAMETER        VALUE------------------------------ ------------------------------nls_date_format         dd-mon-rrnls_calendar       gregoriannls_numeric_ characters       .,nls_nchar_characterset        AL16UTF16NLS_CHARACTERSET       ZHS16GBKNLS_ISO_CURRENCY        AMERICANLS_CURRENCY        $NLS _ Territory       americanls_language     &nBsp; american20 rows selected. Sql>

From Props$, is the character set that represents the database.

2. How to query the DMP file character set
The DMP file exported with the Oracle Exp tool also contains character set information, and the 2nd and 3rd bytes of the DMP file record the character set of the DMP file. If the DMP file is small, such as only a few m or dozens of m, you can open it with UltraEdit (16 binary), look at the 2nd 3rd byte of content, such as 0354, and then use the following SQL to isolate its corresponding character set:

Sql> Select Nls_charset_name (To_number (' 0354 ', ' xxxx ')) from dual; Nls_charset_name (To_number (' 0354 ', ' XXXX '----------------------------------------zhs16gbksql>

If the DMP file is large, such as more than 2G (which is also the most common case), with a text editor opened very slowly or completely open, you can use the following command (on the UNIX host):

Cat Exp.dmp | Od-x | head-1 | awk ' {print $ i} ' | Cut-c 3-6

The corresponding character set can also be obtained with the above SQL statement.

3. Querying the character set of Oracle client side
Under the Windows platform, it is the Nls_lang of the corresponding oraclehome in the registry. You can also set it yourself in a DOS window, such as:

Set Nls_lang=american_america. Zhs16gbk

This will only affect the environment variables in this window.
Under the UNIX platform, it is the environment variable Nls_lang.

$echo $NLS _lang American_america. Zhs16gbk

If the result of the check finds that the server side is inconsistent with the client-side character set, uniformly modify the same character set as the server side.

4. Modifying the character set of Oracle
Once the database is created, the character set of the database is theoretically immutable. Therefore, it is important to consider which character set to use at the beginning of design and installation. According to Oracle's official instructions, the conversion of character sets is supported from subsets to superset, not vice versa. If there are no subsets and superset relationships between the two character sets, then the conversion of the character set is not supported by Oracle. For database server, the incorrect modification of the character set will result in a lot of unpredictable consequences that can seriously affect the normal functioning of the database, so be sure to verify that there are subsets and superset relationships between the two character sets before you modify them. In general, we do not recommend modifying the character set of the Oracle database server side unless it is a last resort. In particular, there is no subset and superset relationship between the two character sets ZHS16GBK and zhs16cgb231280 that we use most often, so it is theoretically not supported to convert between the two character sets.
Modifying the server-side character set (not recommended)
Prior to Oracle 8, you could change the character set of a database by directly modifying the data dictionary table props$. But after Oracle8, at least three system tables recorded the database character set information, only the props$ table is not complete, can cause serious consequences. The correct method of modification is as follows:

$sqlplus/nolog Sql>conn/as sysdba; #若此时数据库服务器已启动, execute the shutdown immediate command to close the database server, and then execute 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

Note: If there is no large object, there is no effect on language conversion during use (remember that the set must be supported by Oracle, otherwise it cannot be startup) as above, but may appear ' Ora-12717:cannot ALTER DATABASE National CHARACTER set when NCLOB data exists ' This is a two way to solve this problem, one is to use the Internal_use keyword to modify the locale, and another is to use re-create, But re-create is a bit complicated, so please use Internal_use.

Sql>shutdown IMMEDIATE; Sql>startup MOUNT EXCLUSIVE; 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 National CHARACTER SET internal_use UTF8; Sql>shutdown immediate; sql>startup;

If you do this, the national charset locale is fine.

Modifying the DMP file character set
As mentioned above, the 2nd 3rd byte of the DMP file records the character set information, so the content of the 2nd 3rd byte of the DMP file can be ' deceived ' by the Oracle check. This is done theoretically only from subset to superset can be modified, but in many cases there is no subset and superset of the situation can also be modified, some of our commonly used character sets, such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK basic can be changed. Because the change is only the DMP file, so the impact is not small.

The specific modification method is more, the simplest is to modify the DMP file's 2nd and 3rd bytes directly with UltraEdit.

For example, to change the character set of the DMP file to ZHS16GBK, you can use the following SQL to isolate the 16 code corresponding to that character set:

Sql> Select To_char (nls_charset_id (' ZHS16GBK '), ' XXXX ') from dual; To_ch-----354sql>

Then change the DMP file 2, 3 bytes to 0354.
If the DMP file is large, you cannot open it with your UE, you need to use the method of the program.

View and modify the Oracle database character set