2022年字符集为ZHSGBK的数据库导入到字符集为ALUTF的数据库收集 .pdf

《2022年字符集为ZHSGBK的数据库导入到字符集为ALUTF的数据库收集 .pdf》由会员分享，可在线阅读，更多相关《2022年字符集为ZHSGBK的数据库导入到字符集为ALUTF的数据库收集 .pdf（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、相信大家都对字符集有相当的了解了，废话就不多说了！直接步入正题：这里主要是测试含有 汉字的数据从ZHS16GBK 的数据库导入到字符集为AL32UTF8 数据库.如有我没想到的其他情况，请大家提建议，我继续完善测试实验。测试环境：目标库：英杰的rac 测试库由Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production With the Partitioning,Real Application Clusters,Automatic Storage Management,OLAP,Data Mining a

2、nd Real Application Testing options sysrac1select userenv(language)from dual;USERENV(LANGUAGE)-AMERICAN_AMERICA.AL32UTF8 yangrac1select*from nls_database_parameters;PARAMETER VALUE-NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY$NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS.,NLS_CHARACTER

3、SET AL32UTF8 NLS_CALENDAR GREGORIAN NLS_DA TE_FORMAT DD-MON-RR NLS_DA TE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY$NLS_COM

4、P BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.2.0.1.0 20 rows selected.源库：Release 11.1.0.6.0-Production on Friday,11 March,2011 21:11:49 Conneted to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-Production With the Part

5、itioning,OLAP,Data Mining and Real Application Testing options 名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 9 页 -源库的 nls_ 信息：sysORACL select*from nls_database_parameters;PARAMETER VALUE-NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY$NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS.,NLS_CHARACTERSET ZHS16

6、GBK NLS_CALENDAR GREGORIAN NLS_DA TE_FORMAT DD-MON-RR NLS_DA TE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY$NLS_COMP BINARY

7、NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.1.0.6.0 测试步骤：这里主要是测试含有汉字的数据从ZHS16GBK 导入到AL32UTF8.在源库创建含有汉字的表:yangORACL create table chart(val varchar2(20);表已创建。=创建英文字符数据yangORACL insert into chart 2 select dbms_random.string(l,15)3 from dual

8、4 connect by level insert into chart 2 select 阿里云 as val from 3 dual connect by level select*from chart;VAL-名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 9 页 -ogqodcrhyqaesks zifjvopjwwvydjf oaosnvrzjazdmju ihpjfwpgzxlbosn igimjnzkjbaiivf jxqmgavtpfmlphf izgrniqmuejiysb cydqjrbjldnsucq sikpsorziznhyly bxstvwdqrqmn

9、ylp ziveucwqsjbmlpu qdhdwjoxthivxoe dqideijubqlzeel qtuyuiwnjigrqcj gzgamguukwgogib ufrnfcdtcnmxatg rpuhrxfzzwmajec kqsawluuwltmdbf mlkfeymlhbqsrwj fokunjivnccjhls drlkbryvpklhlst vaigghwmwspounp qpdslsjqszpasgg cevwjirdyowhzep gpktcxauilvpjym oyrrpyxlroflksp hqfctsysbpnzkfi ufcqglfwqgvuwdv nhloooih

10、rndmrcu grrtelwqpgnmazw olzmdcotmziiphj fmohaagtultvxon lwsqzgofvhgzajm vcdeoomgsrzfvjr pzpfqhpenensoij nbvxphjkufcvxmj ydfknwhpnzzpvsw xzhooomfumuckdc rohspaowywmblti dhjctrkkrdlyhvr yzevspqkpuumjzh xqmdpotvouvzgzl rnvcrercdxybwlt zhbdhamarftudbr 名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 9 页 -biykwlracluonab

11、hsdpycatadxhpft wskchunnmvkbprh butuurwduseffzc fosmnbjannddffp 阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 9 页 -阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云已选择 98 行。yangORACL commit;提交完成。yangORACL select dump(

12、阿里云)from dual;DUMP(阿里云)-Typ=96 Len=6:176,162,192,239,212,198=三个汉字 占用 6 个字节=导出操作=Microsoft Windows 版本6.1.7600 版权所有(c)2009 Microsoft Corporation。保留所有权利。C:Usersaaaaset nls_lang=american_america.al32utf8 这里后面测试不加也可以成功导入al32utf8 C:Usersaaaaexpdp yang/yang tables=chart directory=dumpdir dumpfile=zhs16gbk.

13、dmp Export:Release 11.1.0.6.0-Production on Friday,11 March,2011 21:19:04 Copyright(c)2003,2007,Oracle.All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-Production With the Partitioning,OLAP,Data Mining and Real Application Testing options Starting YANG.SYS_E

14、XPORT_TABLE_01:yang/*tables=chart directory=dumpdir dumpfile=zhs16g bk.dmp Estimate in progress using BLOCKS method.Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method:64 KB Processing object type TABLE_EXPORT/TABLE/TABLE.exported YANG.CHART 6.484 KB 98 rows Mas

15、ter table YANG.SYS_EXPORT_TABLE_01 successfully loaded/unloaded*Dump file set for YANG.SYS_EXPORT_TABLE_01 is:D:DUMPZHS16GBK.DMP Job YANG.SYS_EXPORT_TABLE_01 successfully completed at 21:19:20=导入到目标库=oraclerac1:rac1/tmp/dumpexport NLS_LANG=AMERICAN_AMERICA.AL32UTF8 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 9 页

16、 -=.bash_profile 文件里已经声明了，这里是强调一下。oraclerac1:rac1/tmp/dumpimpdp yang/yang tables=chart directory=dumpdir dumpfile=zhs16gbk.dmp log=zhs16gbk_to_al32utf8.log Import:Release 11.2.0.1.0-Production on Fri Mar 11 21:23:13 2011 Copyright(c)1982,2009,Oracle and/or its affiliates.All rights reserved.Connecte

17、d to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production With the Partitioning,Real Application Clusters,Automatic Storage Management,OLAP,Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters:Legacy Mode Parameter:log=zhs16gbk_to_

18、al32utf8.log Location:Command Line,Replaced with:logfile=zhs16gbk_to_al32utf8.log Master table YANG.SYS_IMPORT_TABLE_01 successfully loaded/unloaded Starting YANG.SYS_IMPORT_TABLE_01:yang/*tables=chart directory=dumpdir dumpfile=zhs16gbk.dmp logfile=zhs16gbk_to_al32utf8.log Processing object type TA

19、BLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA.imported YANG.CHART 6.484 KB 98 rows Job YANG.SYS_IMPORT_TABLE_01 successfully completed at 21:23:17=在目标库中验证=sysrac1select dump(阿里云)from dual;DUMP(阿里云)-Typ=96 Len=9:233,152,191,233,135,140,228,186,145 阿里云三个汉字占用9 个字节。yangrac1

20、select table_name from user_tables;TABLE_NAME-CHARSET CHART yangrac1select val from chart;VAL-ogqodcrhyqaesks zifjvopjwwvydjf oaosnvrzjazdmju ihpjfwpgzxlbosn igimjnzkjbaiivf jxqmgavtpfmlphf izgrniqmuejiysb 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 9 页 -cydqjrbjldnsucq sikpsorziznhyly bxstvwdqrqmnylp ziveucwqsj

21、bmlpu qdhdwjoxthivxoe dqideijubqlzeel qtuyuiwnjigrqcj gzgamguukwgogib ufrnfcdtcnmxatg rpuhrxfzzwmajec kqsawluuwltmdbf mlkfeymlhbqsrwj fokunjivnccjhls drlkbryvpklhlst vaigghwmwspounp qpdslsjqszpasgg cevwjirdyowhzep gpktcxauilvpjym oyrrpyxlroflksp hqfctsysbpnzkfi ufcqglfwqgvuwdv nhloooihrndmrcu grrtel

22、wqpgnmazw olzmdcotmziiphj fmohaagtultvxon lwsqzgofvhgzajm vcdeoomgsrzfvjr pzpfqhpenensoij nbvxphjkufcvxmj ydfknwhpnzzpvsw xzhooomfumuckdc rohspaowywmblti dhjctrkkrdlyhvr yzevspqkpuumjzh xqmdpotvouvzgzl rnvcrercdxybwlt zhbdhamarftudbr biykwlracluonab hsdpycatadxhpft wskchunnmvkbprh butuurwduseffzc fo

23、smnbjannddffp 阿里云阿里云名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 9 页 -阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云阿里云名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 9 页 -阿里云阿里云阿里云98 rows selected.小结：字符集子集向其超集转换是可行的，如此例ZHS16GBK 转换为 AL32UTF8。导出使用的字

24、符集将会记录在导出文件中，当文件导入时，将会检查导出时使用的字符集设置，如果这个字符集不同于导入客户端的NLS_LANG 设置，字符集将根据导入客户端NLS_LANG设置进行转换，如果必要，在数据插入数据库之前会进行进一步转换。通常在导出时最好把客户端字符集设置得和数据库端相同，这样可以避免在导出时发生不必要的数据转换，导出文件将和数据库具有相同的字符集。即使将来会把导出文件导入到不同字符集的数据库中，这样做也可以把转换延缓至导入时刻。当进行数据导入时，主要存在以下两种情况:1.源数据库和目标数据库具有相同字符集设置这时，只需要设置NLS_LANG等于数据库字符集即可导入(前提是，导出使用的是

25、和源数据库相同字符集，即三者相同)2.源数据库和目标数据库字符集不同如果我们导出时候使用的NLS_LANG是和源数据库相同的字符集，那么导入时就可以设置客户端 NLS_LANG等于导出时使用的字符集，这样转换只发生在数据库端，而且只发生一次。例如:如果进行从ZHS16GBK 到 UTF8 的转换1)使用 NLS_LANG=AMERICAN_AMERICA.ZHS16GBK导出数据库。这时创建的导出文件包含ZHS16GBK 的数据2)导入时使用NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 这时转换仅发生在insert 数据到 UTF8 的数据库中。以上假设的转换只在目标数据库字符集是源数据库字符集的超集时才能转换。参考文章：http:/ http:/ http:/ 名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 9 页 -