Shulou(Shulou.com)06/02 Report--

Modify the SERVER character set of the database

The database character set cannot be changed in principle after it is created. If you need to modify the character set, you usually need to export the database data, rebuild the database, and then import the database data to convert, or modify the character set through the ALTER DATABASE CHARACTER SET statement, but after creating the database to modify the character set is limited, only the new character set is the superset of the current character set can modify the database character set, for example, UTF8 is the superset of US7ASCII, modify the database character set can be used ALTER DATABASE CHARACTER SET UTF8. According to Oracle's official statement, character set conversion from subset to superset is supported, and vice versa. If there is no subset and superset relationship between the two character sets, then character set conversion is not supported by oracle.

For the database server, incorrect modification of the character set will lead to many unpredictable consequences, which may seriously affect the normal operation of the database, so be sure to confirm whether there is a subset and superset relationship between the two character sets before modification. In general, we do not recommend modifying the character set on the server side of the oracle database unless you have to.

In particular, there is no subset and superset relationship between the two most commonly used character sets, ZHS16GBK and ZHS16CGB231280, so theoretically, the conversion between the two character sets is not supported.

1 modify the server character set method

Before oracle 8, you could change the character set of the database by directly modifying the data dictionary table props$. However, after oracle8, at least three system tables record the information of the database character set, and only changing the props$ table is not complete, which may lead to serious consequences. The correct modification method is as follows:

$sqlplus / nolog

SQL > conn / as sysdba

If the database server is started at this time, first execute the SHUTDOWN IMMEDIATE command to shut down 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

2 modify the server character set method (you can skip the check of superset)

Using INTERNAL_USE can skip the check of superset. After adding this to ALTER DATABASE character set INTERNAL_USE, the problem of modification can be solved completely, even if an error is reported. Such as:

ALTER DATABASE character set INTERNAL_USE ZHS16CGB231280

ALTER DATABASE character set INTERNAL_USE zhs16gbk

SQL > connect sys/oracle as sysdba

SQL > startup mount

SQL > alter session set sql_trace=true

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 > set linesize 120

SQL > alter database character set ZHS16GBK

Error report:

Alter database character set zhs16gbk

ERROR at line 1:

ORA-12712: new character set must be a superset of old character set

# you can skip the check of superset using INTERNAL_USE, ALTER DATABASE character set INTERNAL_USE

SQL > ALTER DATABASE character set INTERNAL_USE zhs16gbk

SQL > shutdown immediate

SQL > STARTUP

SQL > select name,value$ from props$ where name like'% NLS%'

NLS_CHARACTERSET

Zhs16gbk

Second client character set (NLS_LANG parameter)

1 client character set meaning

The client character set defines the encoding method of the client character data. Any character data sent from or sent to the client is encoded using the character set defined by the client. The client can be regarded as a variety of applications that can be directly connected to the database, such as sqlplus,exp/imp and so on. The client character set is set by setting the NLS_LANG parameter.

2 query the character set of oracle client

This is relatively simple. Under the windows platform, it is the NLS_LANG of the corresponding OracleHome in the registry.

You can also set it yourself in the dos window, such as:

Set nls_lang=AMERICAN_AMERICA.ZHS16GBK

This only affects 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 character set on the server side is not consistent with the character set on the client side, please modify it to the same character set as the server side.

3 client character set setting method

1) UNIX environment

$NLS_LANG= "simplified chinese" _ china.zhs16gbk

$export NLS_LANG

Edit the profile file for the oracle user

Or temporarily change the export NLS_LANG= "SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

2) Windows environment

Edit the registry

Regedit.exe---HKEY_LOCAL_MACHINE---SOFTWARE---ORACLE-HOME0

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.