oracle创建外部表

创建外部表

1.在操作系统创建3个目录

[oracle@hxy ~]$ cd ext
[oracle@hxy ext]$ mkdir data
[oracle@hxy ext]$ mkdir log
[oracle@hxy ext]$ mkdir bad

2.在/home/oracle/ext/data下创建两个数据源文件

[oracle@hxy ext]$ cd data/
[oracle@hxy data]$ touch empxt1.dat
[oracle@hxy data]$ touch empxt2.dat

添加数据：

[oracle@hxy data]$ vi empxt1.dat 


001,haoxiaoyu,clerk,7902,17-DEC-80,100,0,20
002,haoxiaoyu1,clerk,7902,17-DEC-80,100,0,20
003,haoxiaoyu2,clerk,7902,17-DEC-80,100,0,20
004,haoxiaoyu3,clerk,7902,17-DEC-80,100,0,20

[oracle@hxy data]$ vi empxt2.dat 


7564,zhangzhongshuai,salesman,7892,28-SEp-81,1250,0,30
7564,ngzhongshuai1,salesman,7892,28-SEp-81,1250,0,30
1243,zhangzhongshuai2,salesman,7892,28-SEp-81,1250,0,30

3.以sys登陆创建3个目录对象

SQL> conn / as sysdba
Connected.
SQL> create or replace directory datadir as '/home/oracle/ext/data';


Directory created.


SQL> create or replace directory logdir as '/home/oracle/ext/log';


Directory created.


SQL> create or replace directory baddir as '/home/oracle/ext/bad';


Directory created.


SQL> grant read on directory datadir to scott;


Grant succeeded.


SQL> grant write on directory logdir to scott;


Grant succeeded.


SQL