1.创建目录并授权
sqlplus /nolog
conn sys/ticket as sysdba
创建目录
create directory etl_data_dir as 'D:\app\Administrator\admin\ticket\ETL';
create directory etl_log_dir as 'D:\app\Administrator\admin\ticket\ETL';
授权
grant write on etl_data_dir to scott;
grant read on etl_data_dir to scott;
查看目录存在
select * from dba_directories;
检查SCOTT的操作权限是否存在
select * from dba_tab_priv 
where table_name in ('ETL_DATA_DIR','ETL_LOG_DIR');

2.创建外部表
conn scott/tiger

创建外部表
create table sales_delta
(
 prod_id number(6),
 cust_ID number,
 time_id date,
 channel_ID char(1),
 promo_ID number(6),
 quantity_sold number(3),
 amount_sold number(10,2))
 organization external
 (
   type oracle_loader
    default directory ETL_DATA_DIR
	access parameters
	(
	  records delimited by newline characterset US7ASCII
	  badfile 'ETL_LOG_DIR' :'sales.bad'
	  logfile 'ETL_LOG_DIR' :'sales.log'
	  fields terminated by " " optionally enclosed by '\t'
	)
	LOCation ('sales_delta.txt')
 )
reject limit unlimited;

查看表的信息
select table_name ,tablespace_name from user_tables;

查看外部表的信息
select table_name,default_directory_owner,default_directory_name from user_external_tables;

3.提供文本数据文件
conn jinfeng/ticket
@sales_detail.sql
创建导出文本文件的sql文件sales_detail.sql,内容如下
set line 120;
set pagesize 49990
set heading off
set feedback off
alter session set nls_date_language='AMERICAN';
spool D:\app\Administrator\admin\ticket\dpdump\sales_delta.txt
select * from  sales where rownum<49990;
spool off

将sales_delta.txt拷贝到对应外部数据文件目录ETL_DATA_DIR中。

4.检查数据是否成功
conn scott/tigger 
select count(1) from sales_delta;

?