迁移数据时Oracle字符集的转换
一览众山小 2012-09-10
按照Oracle官方文档所说，数据库字符集在创建后原则上不能更改。因此，在设计和安装之初考虑使用哪一种字符集十分重要。对数据库server而言，错误的修改字符集将会导致很多不可测的后果，可能会严重影响数据库的正常运行.但是由于每家软件公司使用的字符集一样，迁移数据时肯定是会遇到字符集转换的问题.比如最常遇到是英文字符集转换成中文，中文的转换成英文.而且在迁移数据时都是上百GB的dmp文件啊.
在迁移数据过程中我使用过两种方法一是修改dmp文件的字符集一是修改数据库的字符集.
修改dmp文件字符集,dmp文件的第2第3字节记录了字符集信息，因此直接修改dmp文件的第2第3字节的内容就可以‘骗’过oracle的检查。这样做理论上也仅是从子集到超集可以修改，但很多情况下在没有子集和超集关系的情况下也可以修改，我们常用的一些字符集，如US7ASCII，WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK基本都可以改。因为改的只是dmp文件，所以影响不大。
具体的修改方法比较多，最简单的就是直接用UltraEdit修改dmp文件的第2和第3个字节。
比如想将dmp文件的字符集改为ZHS16GBK，可以用以下SQL查出该种字符集对应的16进制代码: SQL> select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual;
0354 然后将dmp文件的2、3字节修改为0354即可,但是这样种方法只能使用oracle8及以下版本的客户端导出的dmp才能修改
一种是修改数据库字符集,因为官方文档是说创建数据库后是不能修改，但是没办法工作需要在迁移数据时先创建一个中间数据库使用源数据库的字符集进行创建的
将源数据库的数据导入中间数据库后修改中间数据库的字符集使其与目标数据库的字符集一至，再从中间数据库将数据导入目标数据库.我这样做很麻烦,后面有一次由于
目标数据库是没有业务数据所以就直接修改了目标数据库字符集将源数据库的数据导入进来了.
---１---修改数据库里的字符集
---中文字符集转英文字符集
--英文
update sys.props$ set value$='US7ASCII' where name='NLS_CHARACTERSET';
commit;
update sys.props$ set value$='US7ASCII' where name='NLS_NCHAR_CHARACTERSET';
commit;
---２---修改强制同步数据库里的字符集
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
---给CHARSET变量赋值
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
--INTERNAL_USE是没有写在文档中的参数，用以强制完成字符集一致化
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
--ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
-- 再次启动数据库一遍
SHUTDOWN IMMEDIATE;
STARTUP;
set nls_lang=AMERICAN_AMERICA.US7ASCII;
---英文字符集转中文字符集
update sys.props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';
commit;
update sys.props$ set value$='ZHS16GBK' where name='NLS_NCHAR_CHARACTERSET';
commit;
---２---修改强制同步数据库里的字符集
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
---给CHARSET变量赋值
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
--INTERNAL_USE是没有写在文档中的参数，用以强制完成字符集一致化
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
--ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
-- 再次启动数据库一遍
SHUTDOWN IMMEDIATE;
STARTUP;
set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK;
1，修改dmp的时候，注意数据库版本
9i以以下的数据库，需要修改dmp文件第1行的第2和第3个字节
10g以上的数据库，需要修改dmp文件第3行的第3和第4个字节
这个方法比较局限，比如，你的数据库10T，这个方法显然就不行。。。。
2，强制修改是数据库字符集
从10以后，oracle推荐使用csscan
3，补充一个，对于数据量大的数据库整体迁移，这个方法显然不行，仅仅是用于少量数据库或者某些含多字节（如中文）少的表
dblink+utl_raw
这是Lunar_Zhang给我的提供的另外的方法在这附上,谢谢他的方法
