如何创建外部表

1.1 create table语句创建

  首先CREATE TABLE语句有个ORGANIZATION子句，他有三个选项：HEAP 堆组织表（默认）；INDEX索引组织表；EXTERNAL外部组织表。

1.1.1创建directory对象(sysdba账户创建)

   conn / as sysdba

   create directory extdir as 'f:\oracle\script;

    greant read,write on directory extdir to scott;

1.1.2 执行create table语句

 create table ext_case1

(ename varchar2(10),

job varchar2(20),

sal number)

organization external

(type oracle_loader

default directory extdir

access parameers

(records delimited by newline

skip 6

fields terminated by ","

(ename,job,sal)

)

location('ldr_case1.ctl')

);

select * from ext_case1;

 

1.2 sql*loader创建

sqlldr命令有一个参数：external_table,它有三个属性值：NOT_USED,GENERATE_ONLY,EXECUTE

F:\oracle\script>sqlldr scott/tiger control=ldr_case1.ctl external_table=generate_only->

查看ldr_case1.log出现创建外部表的相关脚本。