外部表的相关疑惑

1.1 外部表有加载日志吗

      有，分别对应ACCESS PARAMETERS子句中的LOGFILE,DISCARDFILE,BADFILE三个参数，不指定的话系统按一定规则生成：“表名+进程ID+.log”

查询外部表ext_case1,就会在extdir目录下自动生成一个ext_case1_[pid].log文件。

为了操作方便，再创建一个外部表来读取输出的日志文件，操作如下：

首先修改表的定义，显式加上日志文件名称：

alter table ext_case1 access parameters
(
RECORDS DELIMITED BY NEWLINE
SKIP 6
LOGFILE 'EXT_CASE1.LOG'
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
);

查询表对象的定义：select dbms_metadata.get_ddl('TABLE','EXT_CASE1') FROM DUAL;

PS:如果要修改ACCESS PARAMETERS 子句中参数的话，该子句中参数必须重新指定。

建存放日志的表：

CREATE TABLE EXT_CASE1_LOG(LNAME VARCHAR2(4000))--因oracle中普通字符类型最大长度不超过4000个字符，因此仅读取日志文件中每行的前4000个字符，如果单行长度超出，可通过创建多个列的方式解决
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTDIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
NOLIGFILE
NOBADFILE
NODISCARDFILE
FIELDS TERMINATED BY ","
(LNAME POSITION(1:4000))
)
LOCATION('EXT_CASE1.LOG')
);

查看ext_case1表产生的日志：

select * from ext_case1_log where rownum<=10;

1.2 能像普通表那样修改属性吗

不能，要改必须整个部分都修改

表结构：与普通表操作完全相同，同时ACCESS PARAMETERS中也要做相应修改

访问驱动：一般不用改，改Directory的语法：ALTER TABLE etx_tbl_name DEFAULT DIRECTORY dir_name;

加载参数：全部改

1.3 有多个文件要加载怎么办

执行：ALTER TABLE EXT_CASE1 LOCATION('ldr_case1.ctl','ext_case1_1.dat');--多个文件用逗号分隔，数据文件中的格式必须一样

1.4 外部表加载的实际效率

CREATE TABLE EXT_BIGTBL
(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(50),
OBJECT_ID NUMBER,
CREATED DATE,
STATUS VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTDIR
ACCESS PARAMETERS
(
 RECORDS DELIMITED BY NEWLINE
 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
 (OWNER,
 OBJECT_NAME,
 OBJECT_ID,
 CREATED CHAR(255) DATE_FORMAT DATE MASK 'YYYY-MM-DD HH24:MI:SS',
 STATUS)
)
LOCATION('LDR_OBJECT.CSV')
);

scott@JSSBOOK> SELECT COUNT(0) FROM EXT_BIGTBL;
  COUNT(0)
----------
    903160

scott@JSSBOOK> TRUNCATE TABLE OBJECTS;


表被截断。


已用时间:  00: 00: 00.35
scott@JSSBOOK> INSERT INTO OBJECTS(
  2  OWNER,OBJECT_NAME,OBJECT_ID,STATUS,CREATED)
  3  SELECT OWNER,OBJECT_NAME,OBJECT_ID,STATUS,CREATED FROM EXT_BIGTBL;
INSERT INTO OBJECTS(
*
第 1 行出现错误:
ORA-01654: 索引 SCOTT.IDX_OBJ_OWNER_NAME 无法通过 128 (在表空间 SCOTT_TBS 中) 扩
展

sys@JSSBOOK> alter tablespace scott_tbs add datafile '%ORACLE_HOME%\oradata\scot
t_tbs_02.dbf' size 500M;

scott@JSSBOOK> INSERT INTO OBJECTS(
  2  OWNER,OBJECT_NAME,OBJECT_ID,STATUS,CREATED)
  3  SELECT OWNER,OBJECT_NAME,OBJECT_ID,STATUS,CREATED FROM EXT_BIGTBL;
已创建903160行。
已用时间:  00: 01: 14.28

scott@JSSBOOK> rollback;
回退已完成。
scott@JSSBOOK> insert /*+ APPEND */ into objects(
  2  owner,object_name,object_id,status,created)
  3  select owner,object_name,object_id,status,created from ext_bigtbl;

已创建903160行。

已用时间:  00: 00: 35.78

外表加载主要有三个方面影响加载速度：CPU性能，内存，I/O能力

1.5 怎样用外部表实现数据加载/卸载

卸载：

create table ext_bigtbl_dp
organization external
(
type oracle_datapump
default directory extdir
location ('ext_bigtbl_dp.dmp')
)as select * from objects;

 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EXT_BIGTBL_DP') FROM DUAL;--生成创建语句

将生成的ext_bigtbl_db.dmp文件复制到目标数据库的适当路径下，并执行上一步生成的创建语句。PS:要保存在名为extdir的Directory对象，当然也可以修改为其他拥有操作权限的Directory对象

执行：

insert /*+ APPEND */ INTO OBJECTS SELECT * FROM EXT_BIGTBL_DP1;

会发现速度大大提升。