1.
--创建目录
CREATE OR REPLACE DIRECTORY MY_PATH AS '/home/oracle';
2. 编辑要导入数据文件extTab.txt,内容如下:
3333,TEST_001,SALE,10
2222,TEST_999,MANAGER,1
1111,TEST_002,DEV,22
3. 将文件extTab.txt放到/home/oracle目录下
4. 进入sqlplus下,执行下面的语句创建外部表TEST_TMP
CREATE TABLE test_tmp (empno varchar2(8),ename varchar2(8),job varchar2(8),deptno varchar2(8))
organization external
(type oracle_loader
default directory MY_PATH
access parameters
(records delimited by newline
fields terminated by ','
missing field values are NULL
(empno,ename,job,deptno))
location ('extTab.txt'))
reject limit unlimited;
注: 表字段TEST_TMP不要为INTEGER,否则会报错,数据也无法加载
外部表无法做DML操作,建议使用CTAS或INSERT INTO .. SELECT创建数据库的表来保存数据