Oracle Character Set modification (ORA-29275)

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.