Oracle Character Set modification (ORA-29275)

I have a friend today. The database appears ORA-29275 partial multibyte character, corresponding field can only use To_char to query normally, feel is character set problem. It is true that the character set has been modified.

His way of modification:

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;

To view the database:

Sql> Select name,value$ from props$ where name is like '%nls% '; name value$---------------------- --------------------------------------nls_language americannls_territory AMERICANLS_CU               Rrency $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_nc HAR_CONV_EXCP Falsenls_nchar_characterset al16utf16nls_rdbms_version 11.2.0.1.0

It has indeed been modified. But here:

ALTER DATABASE CHARACTER SET internal_use ZHS16GBK;

It is very problematic to skip the character set subset check and force the modification.

So there will be problems with future data.

So we use Exp/imp to specify a character set for conversion when exporting?

Introduce a partial paragraph of an article:
Versions prior to 9i: the character set (user data and dictionary data) of all data is converted when the character sets of the source database and the Nls_lang settings of the session in export are not the same;
In the import process, if the import session's Nls_lang and export are inconsistent, the character set in DMP will be converted to the character set of the import session's Nls_lang;
When the Nls_lang of the import session and the character set of the target database are inconsistent, the Nls_lang character set of the public import session will occur to the target database character set conversion
9i and later versions: the character set of the source database and the Nls_lang settings of the export session are not the same, only the dictionary data will occur character set conversion, the user data is consistent with the character set of the source database, ignoring the Nls_lang settings;
In the import process, if the import session's Nls_lang and export are inconsistent, the character set in DMP will be converted to the character set of the import session's Nls_lang;
When the Nls_lang of the import session and the character set of the target database are inconsistent, the Nls_lang character set of the public import session will occur to the target database character set conversion

It's okay, no matter what method you use to convert, as long as the converted character set is not a superset of the original character set is problematic:

So when we modify the database character set, we execute the following statement:
ALTER DATABASE CHARACTER SET ZHS16GBK

If not reported:

[Email protected]>alter DATABASE CHARACTER SET ZHS16GBK; ALTER DATABASE CHARACTER set zhs16gbk*error at line 1:ora-12712:new CHARACTER set must bes a superset of old CHARACTER set

That means it can be modified. If you have the above error, we recommend that you do not modify the Force modified character set

Oracle Character Set modification (ORA-29275)