数据expdp/impdp源库与目标库字符集不一致的问题

何帆

唐老师:
我现在用expdp/impdp做goldengate数据初始化,而且不能改变库的字符集
源库:
AMERICAN_AMERICA.WE8ISO8859P1
目标库:
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
请问有什么便捷的方法来做吗?谢谢.

botang

expdp/impdp
AMERICAN_AMERICA.WE8ISO8859P1 到 SIMPLIFIED CHINESE_CHINA.ZHS16GBK，没有问题的。（小字符->大字符）

何帆

老师好,数据泵导出后直接导入会有乱码
源库字符集:

源库导出记录:

1. [oracle@fqctdb ggate]$ expdp ctms/123456 directory=dmp tables=ogg_test dumpfile=ogg_test_0728.dmp
   
2. 
   
3. Export: Release 11.2.0.4.0 - Production on Sat Jul 28 14:04:13 2018
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
   
9. and Real Application Testing options
   
10. Starting "CTMS"."SYS_EXPORT_TABLE_02":  ctms/******** directory=dmp tables=ogg_test dumpfile=ogg_test_0728.dmp
    
11. Estimate in progress using BLOCKS method...
    
12. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    
13. Total estimation using BLOCKS method: 64 KB
    
14. Processing object type TABLE_EXPORT/TABLE/TABLE
    
15. Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    
16. Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    
17. Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
18. Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    
19. . . exported "CTMS"."OGG_TEST"                           5.625 KB      13 rows
    
20. Master table "CTMS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
    
21. ******************************************************************************
    
22. Dump file set for CTMS.SYS_EXPORT_TABLE_02 is:
    
23.   /orabak/dump/ogg_test_0728.dmp
    
24. Job "CTMS"."SYS_EXPORT_TABLE_02" successfully completed at Sat Jul 28 14:04:20 2018 elapsed 0 00:00:06
    
25. 
    
26. [oracle@fqctdb ggate]$ cd /orabak/dump/
    
27. [oracle@fqctdb dump]$ scp ogg_test_0728.dmp etl:/orabak/dump/
    
28. oracle@etl's password:
    
29. Permission denied, please try again.
    
30. oracle@etl's password:
    
31. ogg_test_0728.dmp                                                                                 100%  116KB 116.0KB/s   00:00   
    
32. [oracle@fqctdb dump]$

复制代码

目标库导入记录:

1. [oracle@etl dump]$ impdp fqct/123456 directory=dmp dumpfile=ogg_test_0728.dmp remap_schema=ctms:fqct remap_tablespace=USER_DATA:tbs_fqct  table_exists_action=truncate
   
2. 
   
3. Import: Release 11.2.0.4.0 - Production on Sat Jul 28 14:08:41 2018
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
9. Master table "FQCT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
   
10. Starting "FQCT"."SYS_IMPORT_FULL_01":  fqct/******** directory=dmp dumpfile=ogg_test_0728.dmp remap_schema=ctms:fqct remap_tablespace=USER_DATA:tbs_fqct table_exists_action=truncate
    
11. Processing object type TABLE_EXPORT/TABLE/TABLE
    
12. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    
13. . . imported "FQCT"."OGG_TEST"                           5.625 KB      13 rows
    
14. Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    
15. Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    
16. Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
17. Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    
18. Job "FQCT"."SYS_IMPORT_FULL_01" successfully completed at Sat Jul 28 14:08:43 2018 elapsed 0 00:00:01
    
19. 
    
20. [oracle@etl dump]$ sqlplus / as sysdba

复制代码

目标库用zhs16gbk的plsql developer查询看到乱码:


在sqlplus里查看该记录为一个16进制数

1. [oracle@etl ~]$ echo $NLS_LANG
   
2. AMERICAN_AMERICA.ZHS16GBK
   
3. [oracle@etl ~]$ sqlplus fqct/fqct
   
4. 
   
5. SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 28 14:24:50 2018
   
6. 
   
7. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
   
8. 
   
9. 
   
10. Connected to:
    
11. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    
12. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
13. 
    
14. SQL> select userenv('language'),name from fqct.ogg_test t where t.id=305;
    
15. 
    
16. USERENV('LANGUAGE')                                  NAME
    
17. ---------------------------------------------------- ----------
    
18. AMERICAN_AMERICA.ZHS16GBK                            2a
    
19. 
    
20. 
    
21. SQL>

复制代码

botang

何帆 发表于 2018-7-28 14:26
老师好,数据泵导出后直接导入会有乱码
源库字符集:

本来就不应该往WE字符集插入中文。

考虑外部表：

1. create table ctms.ogg_test2
   
2. ORGANIZATION EXTERNAL
   
3.   (
   
4.     TYPE ORACLE_DATAPUMP
   
5.     DEFAULT DIRECTORY dmp
   
6.     LOCATION ('emp1.exp')
   
7.   )
   
8. PARALLEL
   
9. AS
   
10. SELECT *
    
11. FROM   ctms.ogg_test;
    
12. 
    
13. 
    
14. --------
    
15. 
    
16. create table ctms.ogg_test2
    
17. (.....)
    
18. ORGANIZATION EXTERNAL
    
19.   (
    
20.     TYPE ORACLE_DATAPUMP
    
21.     DEFAULT DIRECTORY dmp
    
22.     LOCATION ('emp1.exp')
    
23.   )
    
24. PARALLEL

复制代码

何帆

botang 发表于 2018-7-28 19:28
本来就不应该往WE字符集插入中文。

考虑外部表：

好的 谢谢唐老师