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                   AMERICANNLS_TERRITORY                  AMERICANLS_CURRENCY                   $NLS_ISO_CURRENCY               AMERICANLS_NUMERIC_CHARACTERS         .,NLS_CHARACTERSET               ZHS16GBKNLS_CALENDAR                   GREGORIANNLS_DATE_FORMAT                DD-MON-RRNLS_DATE_LANGUAGE              AMERICANNLS_SORT                       BINARYNLS_TIME_FORMAT                HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY              $NLS_COMP                       BINARYNLS_LENGTH_SEMANTICS           BYTENLS_NCHAR_CONV_EXCP            FALSENLS_NCHAR_CHARACTERSET         AL16UTF16NLS_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.