Oracle数据中外部文本文件入库(Oracle外部表使用)

邬心怡

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;