【Oracle】外部表

1 手动创建外部表

创建外部表使用目录

mkdir -p /home/oracle/ext     --使用ORACLE用户在操作系统中执行

在数据库中指定外部表目录

create directory ext_dir as '/home/oracle/ext';

将外部表目录使用权限给hr用户

grant read,write on directory ext_dir to hr;

创建外部表时如果数据文件不是文本文件，则需要使用preprocessor子句。

1）创建测试数据

vi emp.dat

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
"7369","SMITH","CLERK","7902","1980/12/17","800.00","","20"
"7499","ALLEN","SALESMAN","7698","1981/2/20","1600.00","300.00","30"
"7521","WARD","SALESMAN","7698","1981/2/22","1250.00","500.00","30"
"7566","JONES","MANAGER","7839","1981/4/2","2975.00","","20"
"7654","MARTIN","SALESMAN","7698","1981/9/28","1250.00","1400.00","30"
"7698","BLAKE","MANAGER","7839","1981/5/1","2850.00","","30"
"7782","CLARK","MANAGER","7839","1981/6/9","2450.00","","10"
"7788","SCOTT","ANALYST","7566","1987/4/19","3000.00","","20"
"7839","KING","PRESIDENT","","1981/11/17","5000.00","","10"
"7844","TURNER","SALESMAN","7698","1981/9/8","1500.00","0.00","30"
"7876","ADAMS","CLERK","7788","1987/5/23","1100.00","","20"
"7900","JAMES","CLERK","7698","1981/12/3","950.00","","30"
"7902","FORD","ANALYST","7566","1981/12/3","3000.00","","20"
"7934","MILLER","CLERK","7782","1982/1/23","1300.00","","10"

2）创建外部表

create table emp

(

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_loader --外部表访问驱动类型

default directory ext_dir --默认目录，需提前创建

access parameters --设置访问参数，注意先后顺序，否则出错

(

records delimited by newline  --每一行记录用换行做分隔符

badfile ext_dir:'emp.bad'  --产生的bad文件

discardfile ext_dir:'emp.dsc'  --记录相关错误信息文件的位置

logfile ext_dir:'emp.log'  --产生的日志文件

field names all files ignore --去掉文件中的列头或列名部分

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

reject rows with all null fields --拒绝所有空字段的行

(empno,ename,job,mgr,hiredate char(10) date_format date mask 'yyyy-mm-dd',sal,comm,deptno)) --列定义

location('emp.dat') --指定数据文件位置

)reject limit unlimited; --在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制

##################无注释版本##################

create table emp
(
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_loader 
default directory ext_dir
access parameters 
(
records delimited by newline
badfile ext_dir:'emp.bad'
discardfile ext_dir:'emp.dsc'
logfile ext_dir:'emp.log'
field names all files ignore
fields terminated by ',' optionally enclosed by '"' ldrtrim
reject rows with all null fields
(empno,ename,job,mgr,hiredate char(10) date_format date mask 'yyyy-mm-dd',sal,comm,deptno))
location('emp.dat')
)reject limit unlimited;