提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前提
数据库状态正常,置有数据表
一、控制文件
CSV导出控制文件
vim base_vip_info_csv_out.ctl
# 编辑控制文件追加以下内容
OPTIONS
(
CHARACTER_CODE = 'UTF-8'
)
LOAD DATA
INFILE '/home/dmdba/out/BASE_VIP_INFO_CSV.csv'
INTO TABLE HG.BASE_VIP_INFO
FIELDS '|'
(
ID,
XM,
ZJH,
ZL,
DJSJ,
XYSJ
)
CSV导入控制文件
vim base_vip_info_csv_in.ctl
# 编辑控制文件追加以下内容
OPTIONS
(
CHARACTER_CODE = 'UTF-8'
)
LOAD DATA
INFILE '/home/dmdba/in/BASE_VIP_INFO_CSV.csv'
INTO TABLE HG.BASE_VIP_INFO
FIELDS '|'
TXT导出控制文件
vim base_vip_info_txt_out.ctl
# 编辑控制文件追加以下内容
OPTIONS
(
CHARACTER_CODE = 'UTF-8'
)
LOAD DATA
INFILE '/home/dmdba/out/BASE_VIP_INFO_CSV.txt'
INTO TABLE HG.BASE_VIP_INFO
FIELDS '|'
(
ID,
XM,
ZJH,
ZL,
DJSJ,
XYSJ
)
TXT导入控制文件
vim base_vip_info_txt_in.ctl
# 编辑控制文件追加以下内容
OPTIONS
(
CHARACTER_CODE = 'UTF-8'
)
LOAD DATA
INFILE '/home/dmdba/in/BASE_VIP_INFO_CSV.txt'
INTO TABLE HG.BASE_VIP_INFO
FIELDS '|'
二、数据库表
CREATE TABLE “HG”.”"BASE_VIP_INFO"
(
"ID" NUMBER(20,0),
"XM" VARCHAR2(16),
"ZJH" NUMBER(30,0),
"ZL" VARCHAR2(62),
"DJSJ" DATETIME(6),
"XYSJ" DATETIME(6));
create table “HG”.”"BASE_VIP_INFO"(ID NUMBER(20),XM varchar2(16),ZJH number(30),ZL varchar2(62),DJSJ datetime,XYSJ datetime);
insert into “HG”.”"BASE_VIP_INFO"
select rownum as ID,
dbms_random.string('x',2) as XM,
trunc(dbms_random.value(100000000000000000,999999999999999999)) as ZJH,
decode(trunc(dbms_random.value(1,3)),1,'randm_values',
2, 'abcdefg',
3,'返回值可以是数字,字符串,汉字等'
)as ZL,
to_char(sysdate+rownum/24/3600,'yy-mm-dd hh24:mi:ss') as DJSJ,
systimestamp as XYSJ
from dual CONNECT BY level <=80000000;
commit;
三、数据导入导出
1、CSV方式导出
dmfldr userid=SYSDBA/'"PWD"'@IP:port control=\'/home/dmdba/out/base_vip_info_csv_out.ctl\' LOB_DIRECTORY=\'/dm/dmdbms/dm8\' mode=\'out\'
2、CSV文件导入
dmfldr userid=SYSDBA/'"PWD"'@IP:port control=\'/home/dmdba/in/base_vip_info_csv_int.ctl\'
3、TXT方式导出
dmfldr userid=SYSDBA/'"PWD"'@IP:port control=\'/home/dmdba/out/base_vip_info_txt_out.ctl\' LOB_DIRECTORY=\'/home/dmdba/\' mode=\'out\'
4、TXT文件导入
dmfldr userid=SYSDBA/'"PWD"'@IP:port control=\'/home/dmdba/in/base_vip_info_txt_in.ctl\'
总结
以上为达梦快速加载工具使用内容,本文仅仅简单介绍了dmfldr的使,更多信息请参考https://eco.dameng.com/