外部表的简单介绍

一，简单定义：外部表是在数据库中定义了表结构，真正的数据没有存储在数据库中。
二，关于create table 语句
create table 有三个选项 heap,index，external
heap是创建普通的堆表，是默认选项
index是创建索引表
external是创建外部组织表
三，关于外部表的简单介绍
3.1建立一个directory对象
create or replace directory ext_dir as 'e:/app/external';

grant read,write on directory ext_dir to admin;

3.2创建外部表
create table ext_tab
(name varchar2(10),
job varchar2(10),
age number(4))
organization external
(type oracle_loader
default directory ext_dir
access parameters
(records delimited by newline
skip 6
fields terminated by " "
(name,job,age)
)
location('ldr_test1.dat')
);


#ldr_test1.ctl
load data
infile *
truncate into table ext_tab
fields terminated by " " optionally enclosed by '"'
(name,job,age)
begindata
smith "cleak" 2131
allen saleman 1231
ward persident 1231


SQL> select * from ext_tab;

NAME                 JOB                         AGE
-------------------- -------------------- ----------
smith                "cleak"                    2131
allen                saleman                    1231
ward                 persident                  1231


3.3sqlldr中有一个external_table是关于外部表的使用,它有三个参数
not_used:不使用外部表，通过常规路径加载数据，此参数为默认值
generate_only：sqlldr不执行加载，只生成创建外部表的sql和处理数据的sql，将其保存在log文件中
        log中的sql要修改后才能使用
execute:创建外部表并直接通过外部表方式加载数据


通过sqlldr 生成外部表创建脚本，虽然不能直接用，但是可供参考
C:\Users\cj>sqlldr admin/admin control='e:\app\sqlldr\ldr_test1.ctl' external_ta
ble=generate_only log=e:/app/external/ldr_test1.log

SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 10月 17 15:10:56 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


文件需要 CREATE DIRECTORY 语句
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'e:\app\sqlldr\'


用于外部表的 CREATE TABLE 语句:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EXT_TAB"
(
  "NAME" VARCHAR2(10),
  "JOB" VARCHAR2(10),
  "AGE" NUMBER(4)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ldr_test1.bad'
    LOGFILE 'e:/app/sqlldr/ldr_test1.log_xt'
    READSIZE 1048576
    SKIP 6
    FIELDS TERMINATED BY " " OPTIONALLY ENCLOSED BY '"' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "NAME" CHAR(255)
        TERMINATED BY " " OPTIONALLY ENCLOSED BY '"',
      "JOB" CHAR(255)
        TERMINATED BY " " OPTIONALLY ENCLOSED BY '"',
      "AGE" CHAR(255)
        TERMINATED BY " " OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    'ldr_test1.ctl'
  )
)REJECT LIMIT UNLIMITED


用于加载内部表的 INSERT 语句:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EXT_TAB
(
  NAME,
  JOB,
  AGE
)
SELECT
  "NAME",
  "JOB",
  "AGE"
FROM "SYS_SQLLDR_X_EXT_EXT_TAB"


用于清除由以前的语句创建的对象的语句:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_EXT_TAB"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000



从 星期四 10月 17 15:09:32 2013 开始运行
在 星期四 10月 17 15:09:32 2013 处运行结束

经过时间为: 00: 00: 00.10
CPU 时间为: 00: 00: 00.07



3.4介绍下脚本中的几个参数
type:oracle_loader应用较为广泛，参数比较多。
{1.records指的是句子结束标志 默认为：records delimiter by newline
2.badfile：错误文件名称
3.readsize:读取日志文件的缓存区大小 默认1m大小
4.skip：跳过的记录数，因为我们使用了控制文件，所以前面的控制信息需要跳过
5.fields interminated by " " optionally enclosed by ‘’ 与sqlldr中同意.
6.reject rows with all null fields  所有的空值行记录到bad文件中
7.location:指定数据来源
8.load when<condition> for instance load when(name='joe')
}
oracle_datapump数据泵范式，只能导入oracle专用的dmp数据文件

3.5
如果要修改参数通过：alter table table_name access parameters(重新设置);
for instance 修改skip 6 为skip 0

SQL> alter table ext_tab access parameters
  2  (
  3  records delimited by newline
  4  skip 0
  5  fields terminated by " "
  6  (name,job,age)
  7  );

表已更改。

3.6加载多个文件
在location参数中添加参数文件就行
首先用alter table ext_tab location('ldr_test1.dat','ldr_test2.dat');


SQL> alter table ext_tab location('ldr_test1.dat','ldr_test2.dat');

表已更改。

要加载的数据文件
#ldr_test1.dat
smith "cleak" 2131
allen saleman 1231
ward persident 1231

#ldr_test2.dat
xiaoqi student 16
bawei teacher 24
jiuwei student 21



SQL> select * from ext_tab;

NAME                 JOB                         AGE
-------------------- -------------------- ----------
smith                "cleak"                    2131
allen                saleman                    1231
ward                 persident                  1231
xiaoqi               student                      16
bawei                teacher                      24
jiuwei               student                      21

已选择6行。


3.7关于oracle_datapump加载
oracle_datapump加载不需要向oracle_loader加载那么多参数，显得简洁多了。同事加载的是oracle生成的Dmp文件，
所以加载的速度比同数量级的loader加载速度要快

create table ext_datapump_tab
(name varchar2(10),
job varchar2(10),
age number(4)
)
organization external
(type oracle_datapump
default directory ext_dir
location('filename')
)


3.8外部表不支持除select之外的其他DML语句