Oracle Character Set Modification

Modify the character set of oracle

Oracle character sets are inclusive. For example, us7ascii is a subset of zhs16gbk. From us7ascii to zhs16gbk, there will be no data interpretation problems or data loss. Utf8 should be the largest among all character sets because it is based on unicode and stores double-byte characters (so it occupies more space ).

Once a database is created, the character set of the database cannot be changed theoretically. Therefore, it is important to consider which character set to use at the beginning of design and installation. According to the official instructions of Oracle, Character Set conversion is from a subset to a superset, but not vice versa. If there is no relationship between Subsets and supersets between the two character sets, Character Set conversion is not supported by oracle. For database servers, incorrect Character Set modification may lead to many unpredictable consequences, which may seriously affect the normal operation of the database, therefore, before modification, check whether the two character sets have the relationship between Subsets and supersets. Generally, we do not recommend that you modify the character set of the oracle database server unless you have. In particular, the two most commonly used character sets ZHS16GBK and ZHS16CGB231280 do not have a subset or superset relationship. Therefore, in theory, mutual conversion between these two character sets is not supported.

1. Modify the server character set (not recommended)

Before oracle 8, you can directly modify the data dictionary table props $ to change the character set of the database. However, after oracle8, at least three system tables record the information of the database character set. modifying only the props $ table is incomplete and may cause serious consequences. The correct modification method 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

NOTE: If there are no large objects, there is no impact on language conversion during use (remember that the character set must be supported by ORACLE, or cannot be started, however, a message like 'ora-12717: Cannot alter database national character set when NCLOB data exists' may appear.

There are two ways to solve this problem:

1. Use the INTERNAL_USE keyword to modify the region settings,

2. Another method is to use re-create, but re-create is a little complicated, so 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 follow the above steps, the National charset region settings will be fine.

2. Modify the dmp file Character Set

As mentioned above, the 2nd 3rd bytes of the dmp file records the character set information. Therefore, you can directly modify the 2nd 3rd bytes of the dmp file to 'Cheat 'the oracle check. In theory, this can be modified only from the subset to the superset, but in many cases it can be modified without the subset and superset relationships. Some of our commonly used character sets, such as US7ASCII, WE8ISO8859P1, ZHS16CGB231280, and ZHS16GBK can be modified. Because only the dmp file is changed, it has little impact.

There are many specific modification methods. The simplest is to directly use UltraEdit to modify the 2nd and 3rd bytes of the dmp file.

For example, if you want to change the dmp character set to ZHS16GBK, you can use the following SQL statement to find the hexadecimal code corresponding to this character set: SQL> select to_char (nls_charset_id ('zhs16gbk'), 'xx ') from dual;

0354

Modify the 2 and 3 bytes of the dmp file to 0354.

If the dmp file is large and cannot be opened with ue, you need to use the program method.
The two methods described above can be used to modify the character set in Oracle databases. I believe that after you have learned this article, you will be able to easily deal with the character set modification in Oracle databases, if you have more and better methods, share them with you.