四. 创建外部表

 

sqlldr 和外部表

首先给用户准备一个目录

sql>create directory extdir as '/opt/ora10g/oradata/sqlldr';
sql>grant read,write on directory extdir to aegis;

sqlldr可以生成外部表脚本，脚本本身不可执行，但是可以用来作为外部表建立语句的参考

[oracle@aegis sqlldr]$ sqlldr aegis control=ext_ctl.ctl external_table=generate_only
Password:

后会生成一个外部表脚本，需要注意的是：

   1. ext_ctl.ctl是一个sqlldr导入的格式正确的控制文件
   2. 虽然只是生成一个参考，但是作为ext_ctl.ctl里的导入的表和数据也得实际在数据库里存在
   3. 这句话执行完以后，除了会生成一个ext_ctl.log 然后就几乎什么事情都不会发生

总之就是这句命令把最后一句去external_talbe=generate_only掉的话必须能正确的导入到数据库里的！

 

然后cat一下ext_ctl.log，就能看到一个脚本了，好开心，不用敲那么多字了...
所以说，懒人只要找个控制文件敲个external_table=generate_only就可以了！

 

CREATE TABLE "SYS_SQLLDR_X_EXT_GUNDAM"       --表名
(
  "NAME" VARCHAR2(20),
  "PILOT" VARCHAR2(20)
)
ORGANIZATION external                                      --指定现在生成的是一个外部表
(
  TYPE oracle_loader                                            --数据加载方式，还有一种是oracle_datapump
  DEFAULT DIRECTORY EXTDIR                      --外部表的目录啦，刚才建的那个
  ACCESS PARAMETERS                                  --开始设置参数了，这里是个脚本，所以什么参数都有
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK     --记录结束标记，默认就是这个，可以省略
    BADFILE 'EXTDIR':'ext_ctl.bad'                                          --badfile路径，同sqlldr，可以省略
    LOGFILE 'ext_ctl.log_xt'                                    --logfile路径，同sqlldr，可以省略，这两个日志省略的话会自动生成奇怪名字的文件...
    READSIZE 1048576                                           --读取日志缓冲区大小，默认1M,可以省略
    SKIP 6                                                                   --跳过记录条数，同sqlldr，可省略
    FIELDS TERMINATED BY "," LDRTRIM          --同sqlldr控制文件
    REJECT ROWS WITH ALL NULL FIELDS      --如果某列有值为空则不加载
    (
      "NAME" CHAR(255)
        TERMINATED BY ",",
      "PILOT" CHAR(255)
        TERMINATED BY ","
    )
  )
  location                                                                    --数据（控制）文件位置 ，实际用的时候也可以用一个.dat的数据文件
  (                                                                                --这里是因为用sqlldr自动生成脚本，所以必须用一个正确的控制文件
    'ext_ctl.ctl'
  )
)REJECT LIMIT UNLIMITED                                  --可接受的错误，默认0

另外，要说明的是如果你真用一个控制文件去当作外部表的数据而且，不写reject limit unlimited
那么显然会发生错误的（控制文件里那一堆load data什么的..）
但是就算表创建的有错，执行完后依然会显示
table create
然后你如果去select * 的话就会报错
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
啊啊好可爱...脑袋里突然浮现出一个做错事的Oracle娘弱弱的撒谎报告的画面
好吧，Oracle你又来卖萌了....其实提示的也很清楚了 reject limit reached

这个例子还有还有一个提示就是，就算它卖萌告诉你成功了，也要去看一看，不要被oracle萌翻了

 

补充，外部表还可以创建dmp文件

create table 表名

organization external

(

type oracle_datapump

default dirctory dirctory对象名

location('dmp文件名.dmp')

)

as select * from 表A

就完成了对表A的dmp文件的生成

通过DMP生成表的语句和上面差不多

就是create里面要写全列名

另外

select dbms_metadata.get_ddl('TABLE','表名') from dual;可以查看建表语句

但是需要注意

1.  用户需要有select_catalog_role的权限

2. 'TABLE'，'表名'需要大写，要不然会报错