db2 导出oracle导入,将DB2中某表单导入到ORACLE中

项目需求：

将DB2中表单(descartes)数据导入到Oracle中。

实现思路：

1、将DB2数据导出为CSV格式；

2、将CSV格式数据导入到Oracle中。

实现方法：

1、--[DB2导出]--

db2cmd

db2 connect to fgws user db2admin using bitservice

db2 export to c:\temp\descartes.csv of del select * from estate.descartes

2、--[Oracle导入]--

host sqlldr userid=pubr/bitservice@ttonline control=c:\temp\descartes.ctl

3、--[Oracle导出]--

exp system/bitservice@ttonline tables=(pubr.descartes) file=c:\descartes_xy_20111018.dmp log=c:\descartes_xy_20111018.log buffer=655000 compress=y

附录(descartes.ctl)：

load data

infile "c:\temp\descartes.csv"

into table descartes

fields terminated by "," optionally enclosed by '"'

trailing nullcols

(

de_id ,

dpid ,

ipid ,

ac_id ,

ac_cat_biz ,

ac_name_biz ,

og_id ,

og_category ,

og_name ,

og_idcard ,

og_idcardno ,

og_regstatus ,

ps_id ,

ps_category ,

ps_name ,

ps_idcard ,

ps_idcardno ,

ps_nationality ,

ps_place ,

ps_buyer_kind ,

ps_regstatus ,

hs_id ,

hs_code ,

dv_name_province ,

dv_name_city ,

dv_code_district ,

dv_name_district ,

bd_cat_street ,

bd_cat_project ,

bd_code_planning_security_ci ,

hs_startfloor ,

hs_endfloor ,

hs_code_unit ,

hs_code_planning_security_ci ,

bd_floors ,

hs_planning_use ,

hs_structure ,

bd_completetime ,

hs_regstatus ,

re_category ,

re_housebookcode ,

re_housebooktime date "yyyy-mm-dd hh24:mi:ss",

re_housecertificatecode ,

re_cat_house ,

re_coownershiptype ,

re_shareproportion ,

re_houseacquirementtype ,

re_payment ,

re_loanmethod ,

re_housepropertystartdate date "yyyy-mm-dd hh24:mi:ss",

re_housepropertyenddate date "yyyy-mm-dd hh24:mi:ss",

re_landcertificatecode ,

re_statelandacquirementtype ,

re_collectivelandacquirementt ,

re_parcelpropertytype ,

re_landpropertystartdate date "yyyy-mm-dd hh24:mi:ss",

re_landpropertyenddate date "yyyy-mm-dd hh24:mi:ss",

re_regstatus ,

re_completetime date "yyyy-mm-dd hh24:mi:ss",

hs_buildarea ,

hs_usearea ,

re_quality

)