环境说明:
操作系统:OEL5.6 数据库:11.2
db11g数据库字符集:WE8MSWIN1252
emrep数据库字符集:ZHS16GBK
测试:
1、创建测试用户和表:
SYS@db11g>create user ocm identified by ocm;
User created.
SYS@db11g>grant dba to ocm;
Grant succeeded.
SYS@db11g>conn ocm/ocm
Connected.
OCM@db11g>create table T ( x int);
Table created.
OCM@db11g>insert into T values (1);
1 row created.
OCM@db11g>insert into T values (2);
1 row created.
OCM@db11g>commit;
2、查看db11g的库的字符集:
SYS@db11g>SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
3、exp工具导出db11g数据库中ocm用户的T表:
[oracle@ocm ~]$ exp ocm/ocm file=T.dmp TABLES=T GRANTS=Y
[oracle@ocm ~]$ ls
Desktop expdat.dmp export.log oradiag_oracle T.dmp
4、连接emrep库创建ocm用户:
SYS@emrep>create user ocm identified by ocm;
User created.
SYS@emrep>grant dba to ocm;
Grant succeeded.
5、查看emrep库的字符集:
SYS@emrep>SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
6、设置emrep库的服务器端,导入时的字符集:
[oracle@ocm ~]$ set nls_lang=AMERICAN_AMERICA.WE8MSWIN1252
7、imp工具将ocm的T表数据导入emrep数据库中:
[oracle@ocm ~]$ imp ocm/ocm file=T.dmp fromuser=ocm touser=ocm
8、查看导入是否成功:
SYS@emrep>select *from ocm.t;
X
----------
1
2
总结:源库字符集跟目标数据库的字符集不同,经常导致源库导出的数据再导入目标库时要报错,原因很简单,字符集不同编码方式不同,有一种比较简单的方法是:设置当前imp导入时:环境的字符集set nls_lang=,这样oracle会进行相关的字符集编码转换。
补充:
查看数据库的字符集:
SYS@db11g>select NAME,VALUE$ from props$ where name ='NLS_CHARACTERSET';
或者:
SYS@db11g>SELECT userenv('language') FROM dual;
By FishExpert
2012.07.25
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26857237/viewspace-736616/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26857237/viewspace-736616/