1、创建目录（create any directory）： SQLgt; create user Oracle identified by oracle; 用户已创建。 SQLgt; grant dba to 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_loaderDEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBKBADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'data.bad'DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'data.dis'LOGFILE 'user_data.log_xt'READSIZE 1048576FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIMMISSING FIELD VALUES ARE NULLREJECT 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，