外部表的几个限制:
1.
2.
3.
外部表的几个明显优势:
1.
2.
一.创建目录
create directory
赋予读写权限给所有人
grant read ,write on directory SHOP_DIR to public;
二.准备需要导入的os文件(实验一已准备好)
[oracle@test64 ext]$ head exp_output.txt
"SYS","ICOL$","2005-10-22 21:44:33","Abel"
"SYS","ICOL$","2005-10-22 21:44:33","Ande"
"SYS","ICOL$","2005-10-22 21:44:33","Atkinson"
"SYS","ICOL$","2005-10-22 21:44:33","Austin"
"SYS","ICOL$","2005-10-22 21:44:33","Baer"
"SYS","ICOL$","2005-10-22 21:44:33","Baida"
"SYS","ICOL$","2005-10-22 21:44:33","Banda"
"SYS","ICOL$","2005-10-22 21:44:33","Bates"
"SYS","ICOL$","2005-10-22 21:44:33","Bell"
"SYS","ICOL$","2005-10-22 21:44:33","Bernstein"
三.用scott用户准备假装想导入的表(格式满足需要导入的数据)
SQL> create table ext_test (owner VARCHAR2(30),OBJECT_NAME
Table created.
四.创建控制文件
对实验一中的控制文件稍作修改即可
[oracle@test64 ext]$ cat ext_test_1.ctl
LOAD DATA
INFILE exp_output.txt
TRUNCATE
INTO TABLE scott.ext_test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
)
五.利用sqlldr生成外部表创建脚本
[oracle@test64 ext]$ sqlldr scott/oracle control=ext_test_1.ctl external_table=generate_only
执行后,ext_test_1.log日志中就有外部表的创建脚本
CREATE TABLE "SYS_SQLLDR_X_EXT_EXT_TEST"
(
)
ORGANIZATION external
(
)REJECT LIMIT UNLIMITED
外部表的名字可以按照自己的要求做修改。外部表每次查询时都会写BADFILE和LOGFILE,有需要的话也可以做修改。
这里将外部表修改为SCOTT.SHOP_EXT
将修改好的文件取名为create_table_shop_ext.sql
六.进入数据库,创建外部表
[oracle@test64 ext]$ sqlplus scott/oracle
SQL> get create_table_shop_ext.sql
SQL> /
Table created.
查看行数
SQL> select count(*) from SCOTT.SHOP_EXT;
----------
5388841 exp_output.txt