Oracle Character Set modification (ORA-29275)

Oracle Character Set modification (ORA-29275)

Today, a friend of the database ORA-29275 part of the multi-byte characters, the corresponding field can only use to_char to query the normal, it is a character set problem. You have modified the character set.

His modification method:

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 INTERNAL_USE ZHS16GBK;

View the database:

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

Name value $
------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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 $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0

It has been modified. But here:

Alter database character set INTERNAL_USE ZHS16GBK;

This is very problematic. Skip the character set subset check and force the modification.

Therefore, problems may occur in future data.

So what if we use exp/imp to specify the character set for conversion during export?

Introduce part of an article:

Versions earlier than 9i: The NLS_LANG settings of the source database character set and the export session are different, and the character set of all data (user data and Dictionary data) is converted;

During the import process, if the NLS_LANG and export of the import session are inconsistent, the character set in dmp will be converted to the character set in the NLS_LANG of the import session;

When the NLS_LANG Character Set of the import session is inconsistent with that of the target database, the NLS_LANG Character Set of the public import session is converted to the character set of the target database.

9i and later versions: The NLS_LANG settings of the source database character set and the export session are different. Only Dictionary data will undergo Character Set conversion, and user data will be consistent with the source database character set, the setting of NLS_LANG is ignored;

During the import process, if the NLS_LANG and export of the import session are inconsistent, the character set in dmp will be converted to the character set in the NLS_LANG of the import session;

When the NLS_LANG Character Set of the import session is inconsistent with that of the target database, the NLS_LANG Character Set of the public import session is converted to the character set of the target database.

In fact, it does not matter. No matter what method you use for conversion, as long as the character set to be converted is not the superset of the original character set, there is a problem:

Therefore, when modifying the database character set, execute the following statement:

Alter database character set ZHS16GBK

If no:

SYS @ zbdba> alter database character set ZHS16GBK;
Alter database character set ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

That means you can modify it. If the preceding error occurs, do not modify the mandatory character set.

Oracle Character Set Analysis

Oracle Database character set research