数据expdp/impdp源库与目标库字符集不一致的问题
唐老师:我现在用expdp/impdp做goldengate数据初始化,而且不能改变库的字符集
源库:
AMERICAN_AMERICA.WE8ISO8859P1
目标库:
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
请问有什么便捷的方法来做吗?谢谢.
expdp/impdp
AMERICAN_AMERICA.WE8ISO8859P1 到 SIMPLIFIED CHINESE_CHINA.ZHS16GBK，没有问题的。（小字符->大字符） 本帖最后由 何帆 于 2018-7-28 15:08 编辑
老师好,数据泵导出后直接导入会有乱码
源库字符集:
源库导出记录:
$ expdp ctms/123456 directory=dmp tables=ogg_test dumpfile=ogg_test_0728.dmp
Export: Release 11.2.0.4.0 - Production on Sat Jul 28 14:04:13 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "CTMS"."SYS_EXPORT_TABLE_02":ctms/******** directory=dmp tables=ogg_test dumpfile=ogg_test_0728.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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CTMS"."OGG_TEST" 5.625 KB 13 rows
Master table "CTMS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for CTMS.SYS_EXPORT_TABLE_02 is:
/orabak/dump/ogg_test_0728.dmp
Job "CTMS"."SYS_EXPORT_TABLE_02" successfully completed at Sat Jul 28 14:04:20 2018 elapsed 0 00:00:06
$ cd /orabak/dump/
$ scp ogg_test_0728.dmp etl:/orabak/dump/
oracle@etl's password:
Permission denied, please try again.
oracle@etl's password:
ogg_test_0728.dmp 100%116KB 116.0KB/s 00:00
$
目标库导入记录:$ impdp fqct/123456 directory=dmp dumpfile=ogg_test_0728.dmp remap_schema=ctms:fqct remap_tablespace=USER_DATA:tbs_fqcttable_exists_action=truncate
Import: Release 11.2.0.4.0 - Production on Sat Jul 28 14:08:41 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "FQCT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
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
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "FQCT"."OGG_TEST" 5.625 KB 13 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "FQCT"."SYS_IMPORT_FULL_01" successfully completed at Sat Jul 28 14:08:43 2018 elapsed 0 00:00:01
$ sqlplus / as sysdba
目标库用zhs16gbk的plsql developer查询看到乱码:
在sqlplus里查看该记录为一个16进制数
$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
$ sqlplus fqct/fqct
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 28 14:24:50 2018
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select userenv('language'),name from fqct.ogg_test t where t.id=305;
USERENV('LANGUAGE') NAME
---------------------------------------------------- ----------
AMERICAN_AMERICA.ZHS16GBK 2a
SQL>
何帆 发表于 2018-7-28 14:26
老师好,数据泵导出后直接导入会有乱码
源库字符集:
本来就不应该往WE字符集插入中文。
考虑外部表：
create table ctms.ogg_test2
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dmp
LOCATION ('emp1.exp')
)
PARALLEL
AS
SELECT *
FROM ctms.ogg_test;
--------
create table ctms.ogg_test2
(.....)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dmp
LOCATION ('emp1.exp')
)
PARALLEL
botang 发表于 2018-7-28 19:28
本来就不应该往WE字符集插入中文。
考虑外部表：
好的 谢谢唐老师
页:
[1]