How to modify the Oracle Character Set

We all know that Oracle character sets are inclusive. For example, us7ascii is a subset of zhs16gbk. There are no problems in data interpretation between us7ascii and zhs16gbk, and no data loss occurs. 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 relation between Subsets and supersets between the two character sets, Oracle 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 common character sets ZHS16GBK and ZHS16CGB231280 do not have a subset or hyper-set relationship. Therefore, in theory, the conversion between these two Oracle character sets is not supported.

Modify the server character set (not recommended)

Before Oracle 8, you can directly modify the data dictionary table props $ to change the Oracle 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 to set the Oracle character set to be supported by Oracle, or you cannot start it, 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

One is to use the INTERNAL_USE keyword to modify the region settings,

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, there will be no problem with the region settings of the National charset. The above content is the description of modifying the Oracle character set. I hope it will help you in this regard.