1、创建目录（create any directory）：
sql> create user oracle identified by oracle;
用户已创建。
sql> grant dba to oracle;
授权成功。
sql> grant create any directory to oracle;
授权成功。
sql> conn oracle/oracle
已连接。
2、创建外部表：
sql> select * from dba_directories;
owner directory_name directory_path
-------------------- ---------------------------------------- -------------------------------------------
--
sys admin_dir c:\ade\aime_vista_ship\oracle/md/admin
sys data_pump_dir d:\oracle\product\10.2.0\admin\orcl\dpdump\
sys bdump d:\oracle\product\10.2.0\admin\orcl\bdump
sys work_dir c:\ade\aime_vista_ship\oracle/work
sql> create table alert_log(text varchar2(400))
2 organization external (
3 type oracle_loader
4 default directory bdump
5 access parameters(
6 records delimited by newline
7 nobadfile
8 nodiscardfile
9 nologfile
10 )
11 location('alert_orcl.log')
12 )
13 reject limit unlimited
14 /
表已创建。
sql> select * from alert_log where rownum
text
------------------------------------------------------------------
dump file d:\oracle\product\10.2.0\admin\orcl\bdump\alert_orcl.log
sun may 16 11:25:15 2010
oracle v10.2.0.3.0 - production vsnsta=0
vsnsql=14 vsnxtr=3
windows nt version v6.0
cpu : 2 - type 586
process affinity : 0x00000000
memory (avail/total): ph:372m/1021m
sun may 16 11:25:15 2010
已选择9行。
3、外部表acess paramter获得方式
sqlldr oracle/oracle control=user.ctl external_table=generate_only
eg:
1、user.ctl(语法 可以使用em dbconsole生成) :
load
infile 'd:\oracle_file\data.txt'
badfile 'd:\oracle_file\data.bad'
discardfile 'd:\oracle_file\data.dis'
errors=50
append
into table user_data
fields terminated by ',' optionally enclosed by ''
trailing nullcols
(user_name varchar2(20),
user_id number)
2、sqlldr oracle/oracle control=user.ctl external_table=generate_only log=user_data.log
你会在日志中发现：
文件需要 create directory 语句
------------------------------------------------------------------------
create directory sys_sqlldr_xt_tmpdir_00000 as 'd:\oracle_file\'
用于外部表的 create table 语句:
------------------------------------------------------------------------
create table sys_sqlldr_x_ext_user_data
(
user_name varchar2(20),
user_id number
)
organization external
(
type oracle_loader
default directory sys_sqlldr_xt_tmpdir_00000
access parameters
(
records delimited by newline characterset zhs16gbk
badfile 'sys_sqlldr_xt_tmpdir_00000':'data.bad'
discardfile 'sys_sqlldr_xt_tmpdir_00000':'data.dis'
logfile 'user_data.log_xt'
readsize 1048576
fields terminated by , optionally enclosed by '' ldrtrim
missing field values are null
reject rows with all null fields
(
user_name char(255)
terminated by , optionally enclosed by '',
user_id char(255)
terminated by , optionally enclosed by ''
)
)
location
(
'data.txt'
)
)reject limit unlimited
，