oracle sqlldr的使用，外部表的使用,使用idea导出数据库的csv格式文件

一.sqlldr 导入数据

1. 简介

SQLLOADER是一个oracle工具，能够将数据从外部数据文件装载到数据库中；

sqlldr的两种使用方式：
(1)只使用一个控制文件，在这个控制文件中包含数据
(2) 使用一个控制文件(作为模板) 和一个数据文件
一般采用第二种方式，数据文件可以是 CSV 文件、txt文件或者以其他分割符分隔的。

2. sqlldr命令和参数说明

cmd命令行输入

 SQLLDR keyword=value [,keyword=value,...]

参数说明

    userid -- ORACLE 用户名/口令
   control -- 控制文件名
       log -- 日志文件名
       bad -- 错误文件名
      data -- 数据文件名
   discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目         (全部默认)
      skip -- 要跳过的逻辑记录的数目  (默认 0)
      load -- 要加载的逻辑记录的数目  (全部默认)
    errors -- 允许的错误的数目         (默认 50)
      rows -- 常规路径绑定数组中或直接路径保存数据间的行数
               (默认: 常规路径 64, 所有直接路径)
  bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)
    silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
    direct -- 使用直接路径                     (默认 FALSE)
   parfile -- 参数文件: 包含参数说明的文件的名称
  parallel -- 执行并行加载                    (默认 FALSE)
      file -- 要从以下对象中分配区的文件
skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)
skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)

commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)
  readsize -- 读取缓冲区的大小               (默认 1048576)
external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NO
T_USED)
columnarrayrows -- 直接路径列数组的行数  (默认 5000)
streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)
multithreading -- 在直接路径中使用多线程
 resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE)
resumable_name -- 有助于标识可恢复语句的文本字符串
resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)
date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)
no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)

3. 使用

(1) 向表导入txt文件内容

创建文件a.txt，内容为

10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

scott用户建表

create table mydept as select * from dept where 1=2;

建立控制文件 a.ctl 内容为

load data infile 'a.txt' into table mydept
insert (DEPTNO char terminated by ',',
	DNAME char terminated by ',',
	LOC char terminated by ',')

或

load data infile 'a.txt' into table mydept
insert fields  terminated by ',' (DEPTNO,DNAME,LOC )

cmd命令行,使用sqlldr装载数据

sqlldr userid=scott/123456@orcl control=a.ctl  log=a.log

(2) 控制文件中的操作类型

insert --为缺省方式，在数据装载开始时要求表为空

append --在表中追加新记录
修改控制文件，追加记录

replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录

truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

二.spool 导出数据

在sql命令行执行

create table myemp as select empno,ename,hiredate from emp where 1=2;

spool c:\1.txt
select empno||','|| ename||','||to_char(hiredate,'YYYY-MM-DD')  from emp;
spool off

经过处理后1.txt

sqlldr导入
建立控制文件1.ctl

options(skip=2)
load data infile '1.txt' into table myemp
insert (EMPNO char terminated by ',',
	ENAME char terminated by ',',
	HIREDATE date "YYYY-MM-DD" nullif(HIREDATE ="NULL"))

三.外部表

1. 简介

外部表中的数据不装入数据库中，数据库中只存储外部表的定义信息，实际的数据位于操作系统的平面文件中，但可以在数据库中，像访问正常表那样，通过select语句来访问操作系统中的平面文件中所含有的数据；

外部表是只读的；

可以使用SQL，PL/SQL和JAVA访问外部表

2. 分类：使用数据泵引擎生成的外部表、根据文本文件创建的外部表

(1) 使用数据泵引擎生成的外部表

sql命令行，查看目录

 select * from  all_directories;

创建外部表

 create table ext_stu
organization external
(
type oracle_datapump--外部表生成方式是数据泵
default directory MY_DIR--默认的路径
location('ext_stu1.dmp','ext_stu2.dmp')--通过路径和文件名生成外部表在操作系统的平面数据文件
)
parallel--并行
as
select * from emp;

把文件EXT_STU1.DMP、EXT_STU2.DMP拷贝到linux系统，执行：

 create table ext_stu_from_orcl
 (	EMPNO    NUMBER(4)   ,                        
	ENAME    VARCHAR2(10) ,                         
	JOB      VARCHAR2(9) ,                         
	MGR      NUMBER(4)  ,                         
	HIREDATE DATE        ,                        
	SAL      NUMBER(7,2) ,                        
	COMM     NUMBER(7,2) ,                        
	DEPTNO   NUMBER(2)     )
organization external
(
type oracle_datapump--外部表生成方式是数据泵
default directory MY_DIR--默认的路径
location('EXT_STU1.DMP','EXT_STU2.DMP')--通过路径和文件名生成外部表在操作系统的平面数据文件
)

可使用select查询

(2) 根据文本文件创建的外部表

文件b.txt数据

10001,Georgi,Facello
10002,Bezalel,Simmel
10003,Parto,Bamford

type oracle_loader --外部文件方式
access parameters --访问外部文件所遵守的格式

创建语句

create table ext_2	
(id number,firstname varchar2(20),lastname varchar2(20))
 organization external
( type oracle_loader 
 default directory MY_DIR
access parameters	
 (records delimited by newline
 fields terminated by ','	
 ) location ('b.txt')	
);

四.使用idea导出oracle数据库的csv格式文件

连接数据库，选中表，可多选

左键点击dump data to file ,在选项栏最下方选择 configure csv format
配置csv format

配置所需的格式后，dump文件