oracle 字符集不同时数据的导入导出impdp

[size=small]impdp数据导入时，如果字符集不同，是无法正常导入的，这时候有一个办法是：在要导入的新库里手动创建表，然后只导入数据

下面这个是一个简单例子：
1.直接手动创建表
2.然后修改字段长
3.，最后导入数据
由于原字符集是NLS_CHARACTERSET---ZHS16GBK，而生产是UTF-8

要知道
GBK 一个汉字占用两个字节
UTF8 一个汉字占用三个字节
所以当字符集变成了UTF8的时候，原先指定的长度可能就会不够用
我这里是将所有varchar2类型的都变长，至少1.5倍

所以我们需要修改字段长度，不然会报错：
ORA-02374: conversion error loading table "NEWCCS"."SRVTRACEHIST"
ORA-12899: value too large for column ABOUT (actual: 2075, maximum: 2000)

ORA-02372: data for row: ABOUT : 0X'B5DAC8FDB7BD20BACED0A1BDE320363638353831383920C0B4'

先只导结构（metadata_only)
SQL> desc newccs.SRVTRACEHIST
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(20)
CALLNO VARCHAR2(20)
CALLTIME DATE
SRVTYPE VARCHAR2(100)
SUBITEM VARCHAR2(100)
ACTION VARCHAR2(100)
ABOUT VARCHAR2(2000)
CUSTID VARCHAR2(12)
CARRYID VARCHAR2(12)
SRCFLOW VARCHAR2(20)
OPID VARCHAR2(10)
OPNAME VARCHAR2(10)
CALLCENTER VARCHAR2(20)
CREATETIME VARCHAR2(19)

修改长度
SQL> alter table newccs.SRVTRACEHIST modify (SRVTYPE VARCHAR2(200),SUBITEM VARCHAR2(200), ACTION VARCHAR2(200), ABOUT VARCHAR2(3000), OPNAME VARCHAR2(30), CALLCENTER VARCHAR2(30), CREATETIME VARCHAR2(30));

全都要改
SQL> desc newccs.SRVTRACEHIST
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(30)
CALLNO VARCHAR2(30)
CALLTIME DATE
SRVTYPE VARCHAR2(200)
SUBITEM VARCHAR2(200)
ACTION VARCHAR2(200)
ABOUT VARCHAR2(3000)
CUSTID VARCHAR2(30)
CARRYID VARCHAR2(30)
SRCFLOW VARCHAR2(30)
OPID VARCHAR2(30)
OPNAME VARCHAR2(30)
CALLCENTER VARCHAR2(30)
CREATETIME VARCHAR2(30)


再导数据（data_only)
Import: Release 11.2.0.3.0 - Production on Mon Mar 24 18:26:23 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** parfile=SRVTRACEHIST_imp_data.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NEWCCS"."SRVTRACEHIST" 495.7 MB 4172381 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 18:28:21


SQL> desc newccs.SRVTRACE
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(20)
CALLNO VARCHAR2(20)
CALLTIME DATE
SRVTYPE VARCHAR2(100)
SUBITEM VARCHAR2(100)
ACTION VARCHAR2(100)
ABOUT VARCHAR2(2000)
CUSTID VARCHAR2(12)
CARRYID VARCHAR2(12)
SRCFLOW VARCHAR2(20)
OPID VARCHAR2(10)
OPNAME VARCHAR2(10)
CALLCENTER VARCHAR2(20)
CREATETIME VARCHAR2(19)
alter table newccs.SRVTRACE modify (CUSTID VARCHAR2(30), CARRYID VARCHAR2(30), SRCFLOW VARCHAR2(30), OPID VARCHAR2(30), OPNAME VARCHAR2(30), CALLCENTER VARCHAR2(30), CREATETIME VARCHAR2(30));

SQL> desc newccs.SRVTRACE
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(30)
CALLNO VARCHAR2(30)
CALLTIME DATE
SRVTYPE VARCHAR2(200)
SUBITEM VARCHAR2(200)
ACTION VARCHAR2(200)
ABOUT VARCHAR2(3000)
CUSTID VARCHAR2(30)
CARRYID VARCHAR2(30)
SRCFLOW VARCHAR2(30)
OPID VARCHAR2(30)
OPNAME VARCHAR2(30)
CALLCENTER VARCHAR2(30)
CREATETIME VARCHAR2(30)


SQL> desc NEWCCS.RECORDINFO
Name Null? Type
----------------------------------------- -------- ----------------------------
AGENTID VARCHAR2(10)
BEGINTIME VARCHAR2(20)
TIMELEN NUMBER(38)
FILENAME VARCHAR2(41)
CALLTYPE NUMBER(3)
CALLNO VARCHAR2(25)
TIMEZONE VARCHAR2(10)


alter table NEWCCS.RECORDINFO modify (AGENTID VARCHAR2(30) ,BEGINTIME VARCHAR2(30) , FILENAME VARCHAR2(60), CALLNO VARCHAR2(40) ,TIMEZONE VARCHAR2(30));


导数时，字符集不同，需要手动创建表，不要导入表结构，经常有触发器失败，数据没办法导入
直接手动创建表，然后修改字段长度，最后导入数据
Import: Release 11.2.0.3.0 - Production on Fri Apr 4 11:47:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** parfile=custphone_imp_data.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NEWCCS"."CUSTPHONE" 399.8 MB 8079759 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 11:49:54
[/size]