Oracle数据中外部文本文件入库(Oracle外部表使用)
1.创建目录并授权sqlplus /nologconn 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_DIRaccess 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 49990set heading offset feedback offalter session set nls_date_language='AMERICAN';spool D:\app\Administrator\admin\ticket\dpdump\sales_delta.txtselect * from sales where rownum<49990;spool off将sales_delta.txt拷贝到对应外部数据文件目录ETL_DATA_DIR中。4.检查数据是否成功conn scott/tigger select count(1) from sales_delta;?