外部表创建操作步骤

1、创建目录：使用具有dba角色的用户创建目录，并授给创建外部表用户htd_edw对目录的读写权限。
drop directory data_dir;
create or replace directory data_dir as '/home/oracle/oradata';
grant read,write on directory data_dir to htd_edw;

说明：路径"/home/oracle/oradata"为oracle下的拥有的就可以，可以使用select * from dba_directories;查看相关的路径。


2、创建外部表：
drop table exter_dqxx;
create table exter_dqxx(dqxx01 varchar2(100),dqxx02 varchar2(100),dqxx03 varchar2(100),dqxx04 varchar2(100),dqxx05 varchar2(100))
organization external
( type oracle_loader
default directory data_dir
access parameters
( records delimited by newline
skip 1
badfile 'exter_dqxx.bad'
discardfile 'exter_dqxx.dis'
logfile 'exter_dqxx.log'
fields terminated by ','
)
location ('env_shop_20151201.csv')--指定外部表加载文件
);


3、上传".csv"文件，并进行查询,若为其它格式，则另存为csv即可。
select * from exter_dqxx;


---------------------------------------------------

drop table env_shop_standy1;
create table env_shop_standy1
(
id number(6),
openid varchar2(1000),
headimgurl varchar2(1000),
user_id integer,
account varchar2(1000),
shopno varchar2(1000),
manager varchar2(1000),
tel varchar2(1000),
shopname varchar2(5000),
address varchar2(5000),
deal varchar2(1000),
lat number(20,17),
lng number(20,17),
instime varchar2(2000),
envgive integer,
monygive integer,
envuse integer,
monyuse integer,
envget integer,
monyget integer,
status integer
)
organization external
( type oracle_loader
default directory wx1212
access parameters
( records delimited by newline
badfile 'env_shopa.bad'
discardfile 'env_shopa.dis'
logfile 'env_shopa.log'
fields terminated by ','
)
location ('env_shop_20151201.csv')--指定外部表加载文件
) reject limit unlimited;

select * from env_shop_standy1;