Modifying the Oracle Character set

Data import times ORA-12899 error, that is, the length of the inserted data exceeds the set length of the field, the actual length is not long, because of the different Oracle character set, the length of the Chinese characters caused by different. Chinese in the ZHS16GBK accounted for 2 bytes, in UTF-8 accounted for 3 bytes, so the Chinese character import UTF-8 character Set database is very easy to appear field length is not enough, the solution is to increase the length of the field, there is to modify the character set.

If there is already a lot of data in the system, it is recommended to modify the field length because the characters in the original database table are garbled when the character set is modified. If you have to modify the character set, you must first export the data until the character set is modified and then re-imported.

The method of viewing the character set can be used with PL/SQL DEVELOP---->reports---->DBA---->nls Database Parameters, as shown:

As shown, the character set is UTF-8, and the method for modifying the character set is as follows:

Modify the database character set to: ZHS16GBK

Start-run->cmd, input sqlplus system/[email protected] as SYSDBA
If the database server is now started, execute the SHUTDOWN IMMEDIATE command to shut down the database server.
Then execute the following command:
Sql>shutdown Immediate
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 ZHS16GBK;
ERROR at line 1:ora-12721:operation cannot execute if other sessions is active
If the above error occurs, use the following method to modify, using Internal_use can skip the check of the superset:
Sql>alter DATABASE CHARACTER SET internal_use ZHS16GBK;
Sql>shutdown IMMEDIATE
Sql>startup

At this point, the character set has been modified, then the character set, the result is as follows:

Modifying the Oracle Character set