点上边蓝字关注我们
本文演示oracle官方的字符集迁移工具DMU
alter database character set internal_use
这个命令到底能不能用呢，在oracle database 19c版本中，无论是non_cdb，还是pdb，通过实践来看，还是能用的。
但是！但是！但是！
有谁敢用呢？
SYS@orcl> startup restrictORACLE instance started.SYS@orcl> alter database character set internal_use zhs16gbk; Database altered.SYS@orcl> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@orcl> startupORACLE instance started.SYS@orcl> select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.ZHS16GBK
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTEDSQL> alter pluggable database pdb1 open;Pluggable database altered.SQL> select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8SQL> alter pluggable database close immediate;Pluggable database altered.SQL> alter pluggable database open read write restricted;Pluggable database altered.SQL> alter session set container=pdb1;Session altered.SQL> alter database character set internal_use zhs16gbk;Database altered.SQL> alter pluggable database pdb1 close;Pluggable database altered.SQL> alter pluggable database pdb1 open;Pluggable database altered.SQL> alter session set container=pdb1;Session altered.SQL> select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.ZHS16GBKSQL>
2.我们来看看19c版本中官方手册sql language reference中alter database characterset的描述
CHARACTER SET, NATIONAL CHARACTER SETYou can no longer change the database character set or the national character set using the ALTER DATABASE statement. Refer to Oracle Database Globalization Support Guide for information on database character set migration.
那么也就是说，这个语法官方已经不支持了。
另外一篇字符集迁移的官方手册是这么说的
Changing the Database Character Set of an Existing DatabaseDatabase character set migration is an intricate process that typically involves three stages: data scanning, data cleansing, and data conversion.Before you change the database character set, you must identify possible database character set conversion problems and truncation of data. This step is called data scanning. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. Some examples of what may be found during a data scan are the number of schema objects where the column widths need to be expanded and the extent of the data that does not exist in the target character repertoire. This information helps to determine the best approach for converting the database character set.After the potential data issues are identified, they need to be cleansed properly to ensure the data integrity can be preserved during the data conversion. The data cleansing step could require significant time and effort depending on the scale and complexity of the data issues found. It may take multiple iterations of data scanning and cleansing in order to correctly address all of the data exceptions.The data conversion is the process by which the character data is converted from the source character set into the target character set representation. Incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set.There are two approaches for migrating the database character set: Migrating Character Data Using the Database Migration Assistant for Unicode Migrating Character Data Using a Full Export and Import
也就是说对已存在的数据库进行字符集的修改，官方只支持dmu和数据泵的方式。
3.我们来体验一下dmu，dmu只能把字符集转换为al32utf8和utf8。
dmu工具在$ORACLE_HOME/dmu目录下，sh dmu.sh，需要图形化界面
连接到要做字符集迁移的数据库，连接方式类似pl/sql developer,但是连接时有报错，原来是要跑一个脚本。
按提示执行脚本
SYS@orcl> @?/rdbms/admin/prvtdumi.plbLibrary created.Package created.No errors.Package body created.No errors.
连接建好以后进入操作界面，首先要安装资料库
选择目标字符集
创建字符集资料库
进入转换助手流程界面，第一步的资料库已经完成。
开始第二步，扫描数据库
完成后，第三部如果没有问题的话，也算是通过了
现在开始最后一步的转换步骤
4.最后确认一下字符集是否转换成功。
[oracle@vm1 dmu]$ sqlplus / as sysdbaSYS@orcl> select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8
转播
