外部表----500W行os数据到oracle的实验（二）

外部表的几个限制：

1.  只能对表进行select，不能进行delete、update、insert这些DML操作。

2.  因为外部表需要在oracle数据库“服务端”创建目录，os文件必须放在这些目录中。即这些文件只能放在数据库服务端。

3.  外部表上不能创建索引。但可以建立视图

 

外部表的几个明显优势：

1.  os文件改变时，外部表内的数据会跟着改变。

2.  外部表虚拟的导入过程极快。对于实验（一）的数据，创建外部表仅花了几秒钟。创建完成后即可对表进行select操作。

 

一.创建目录

create directory SHOP_DIR as '/home/oracle/ext';

赋予读写权限给所有人

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  VARCHAR2(128),
  2  created date,last_name VARCHAR2(25));

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 '"'
(
  OWNER CHAR,
  OBJECT_NAME CHAR,
  CREATED DATE 'yyyy-mm-dd hh24:mi:ss',
  LAST_NAME CHAR
)

 

五.利用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"             --注意，双引号一定要去掉
(
  "OWNER" VARCHAR2(30),
  "OBJECT_NAME" VARCHAR2(128),
  "CREATED" DATE,
  "LAST_NAME" VARCHAR2(25)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SHOP_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16CGB231280
    BADFILE 'SHOP_DIR':'exp_output.bad'
    LOGFILE 'ext_test_1.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "OWNER" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "OBJECT_NAME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "CREATED" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        DATE_FORMAT DATE MASK "yyyy-mm-dd hh24:mi:ss",
      "LAST_NAME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    'exp_output.txt'
  )
)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;

  COUNT(*)
----------
   5388841

[oracle@test64 ext]$ wc -l exp_output.txt
5388841 exp_output.txt