Modify Oracle11g Character Set AL32UTF8 to ZHS16GBK

Modify Oracle11g Character Set AL32UTF8 to ZHS16GBK

This problem occurs during database migration. Source database: oracle11g Personal Edition of win7 64-bit system on your laptop, Character Set ZHS16GBK; target database, HP sqlserver2008 system 64-bit database server, Character Set AL32UTF8.

When a database server of HP is deployed this afternoon, the default Character Set of oracle11g Enterprise Edition is AL32UTF8,

The character set of the oracle11g Personal Edition database installed on my local computer is ZHS16GBK by default. Therefore, when I import the database. dmp file exported from my local computer to the Hp database, I report

Imp error and the error description is garbled in Chinese. Then I checked the information online and learned that the following conditions must be met to solve the Garbled text problem:

The character set formats must be the same for the server and client of the target database, as well as for the dmp files to be imported from my local source database. The character set of my local ground source database is ZHS16GBK and the target server is AL32UTF8, which obviously does not meet the above conditions.

Then, I first try to change the character of the HP Server database in the following way:

Method 1: execute the following statement in sys/sys: (failed)

Note: SQL> alter database CHARACTER SET ZHS16GBK;
Alter database character set ZHS16GBK

The following error is reported:
ERROR is located in row 1st:
ORA-12712: The new character set must be a superset of the old character set.

Note: If the s1 set contains every element in the s2 set and the s1 set may contain elements not included in the s2 set, s1 is the super element of s2. AL32UTF8 contains ZHS16GBK.

Method 2: Use scott/tiger to log on to sqlplus, connect sys/sys as sysdba, and enter the command. If your password is red, use your own password. (This method is successfully tested !)

Then, execute the following SQL statements on the sqlplus interface in sequence:

If the database server has been started, run the shutdown immediate command to shut down the database server, and then run the following command: Note: Each SQL statement has a prompt. Be sure to enter the Command carefully.

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; // skip super subset detection SQL> ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK;

This line does not work, an error occurred after execution ORA-00933: the SQL command ended incorrectly, but the execution of the previous line has taken effect, other articles do not mention this line.
 

　　SQL>SHUTDOWN IMMEDIATE;　　SQL>STARTUP

Restart the oracle11g listener and service, and log on to PL/SQL developer again,

Execute select userenv ('language') nls_lang from dual; it is found that the character set of the target database is as follows, and it has been correctly modified to SIMPLIFIED. CHINESE_CHINA.ZHS16GBK.

NLS_LANG

----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Then execute the corresponding imp ldpssts/ldpssts @ orcl fromuser = lspss touser = ldpssts file = D: \ imp \ ldpssts. dmp log = D: \ imp \ ldpssts. log. The import is successful.