How to change the Oracle character set to avoid garbled characters

Turn the notes of a great God.

The most commonly used Oracle character set ZHS16GBK (GBK 16-bit Simplified Chinese) in the country supports traditional Chinese and stores a Chinese character in 2-character lengths. The UTF8 character set is multi-byte storage, and 1 Chinese characters (simplified, traditional) are sometimes stored in 3-character lengths.
Oracle supports character set changes, but UTF8 is the largest character set in Oracle, meaning UTF8 is a strict superset of ZHS16GBK.
For a subset-to-superset conversion, Oracle is allowed, but the transition to a superset-to-subset is not allowed. Generally for a superset-to-subset conversion, it is recommended to delete the original database by DBCA, re-build the library, select the correct character set, and then import the backup.
My scenario is to back up the data, then force the conversion character set from UTF8 to ZHS16GBK, and then import the backup data. If not, you can rebuild the library, set the character set ZHS16GBK, and import the backup data. If that doesn't work, change the character set from ZHS16GBK to UTF8 (which is safe), and then import the backup data back to the original state. This will probably avoid the hassle of re-building the library.

1. Back up data for all users in the database
Log in as an Oracle user and execute the following command
# Export Nls_lang = "Simplified Chinese_china. UTF8 "
Keep consistent with the database server so that when exp exports, there will be no conversion of characters, back up the most original data.
2. Assess the risk of UTF8 conversion into ZHS16GBK
Before conversion, use Oracle's Csscan tool to scan the database to evaluate possible corruption of the data before and after the character set conversion. If the assessment is bad, you must give up.
Install a set of tables and procedures belonging to the CSMIG user first. Log on to UNIX with Oracle users,
#sqlplus "/As Sysdab"
sql>@ $ORACLE _home/rdbms/admin/csminst.sql
Sql>exit
# $ORACLE _home\bin\csscan-help
You can better understand how to use Csscan.
# $ORACLE _home/bin/csscan system/sunday user=mmsc fromchar=utf8 tochar=zhs16gbk array=102400 PROCESS=3 > Csscan.log
The above command means scanning the user: All data in the MMSc, from the character set UTF8 to the conversion of ZHS16GBK. Then get three files: Scan.txt, Scan.out, Scan.err.
Looking at Scan.out,scan.err, you can see that all the data under the MMSc user can be converted, and there is no conversion of "exceptional", so you can be more assured.
3. Change the character set of the database to ZHS16GBK
As stated earlier, the conversion from superset to subset is not allowed in Oracle by the command "Alter Database characeter set XXXX". However, this command provides a way to do this:
Alter Database Character Set internal_convert/internal_use XXXX

This is a non-public command of Oracle. "When using this command, Oracle skips all subsets and superset checks and casts between any character sets, so you must be very careful when using this command, and you must be aware of the risks that this operation poses".
Log on to UNIX with Oracle users,
#sqlplus "/As SYSDBA"
Sql> SHUTDOWN IMMEDIATE;
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> ALTER DATABASE CHARACTER SET ZHS16GBK;
If you do not use the "internal_use" parameter, the system prompts an error:
ERROR at line 1:
Ora-12712:new character set must be a superset of the old character set
sql> ALTER SESSION SET sql_trace=false;
Sql> SHUTDOWN IMMEDIATE;
Sql> STARTUP;
At this point, check to see if the character set of the database changes.
Sql> Select value$ from props$ where name= ' nls_characterset ';
value$
-----------------
Zhs16gbk
Then check the database in Simplified Chinese, Traditional Chinese is normal, there will be no garbled.
Sql>select Spid,spname,spshortname from Spinfovisual_hk
......
Unfortunately, I saw a bunch of garbled characters, which also proves that Oracle does not support the change of the character set from superset to subset, when the mind is very nervous and afraid of failure, thus reverting back to the original.
However, according to the previous verification, the UTF8 under the import of the backup into the ZHS16GBK, is OK, so continue to try.
4. Import the backed up user data
or to log on to UNIX with an Oracle user, first delete the user mmsc in the library:
#sqlplus "/As SYSDBA"
Sql>drop user MMSc Cascade;
Sql>exit
Run Createuser.sql again to generate the MMSc user.
Then use the original backup file to import into the MMSc User:
Note: Set Nls_lang to be consistent with the current database: ZHS16GBK. Thus, the Nls_lang of the user session at export is UTF8, consistent with the original database character set, and now ZHS16GBK, consistent with the database character set at this time. In this way, character conversions are performed when importing.
# Export Nls_lang = "Simplified Chinese_china. ZHS16GBK "
#imp mmsc/[email protected]File=dsmpd113_user_mmsc.dmp ignore=y fromuser=mmsc Touser=mmsc
See the database now simplified, Traditional Chinese, haha, no garbled, everything shows normal.
The verification is followed by the following: 1 Chinese characters are now only 2 characters long. The problem is solved!

How to change the Oracle character set to avoid garbled characters