关于External Table

实验环境

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

假设一文本文件d:/test.txt含有以下数据：
360,Jane,Janus,ST_CLERK,121,17-5-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-5-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-5-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-5-2001,9000,.15,80,aalda

创建Directory

SQL> create directory dir_test as 'd:/';

目录已创建。

1.创建sqlldr驱动的External Tables

默认情况下，oracle会采用sqlldr驱动的external table

SQL> CREATE TABLE test_ext
2 (employee_id NUMBER(4),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 job_id VARCHAR2(10),
6 manager_id NUMBER(4),
7 hire_date DATE,
8 salary NUMBER(8,2),
9 commission_pct NUMBER(2,2),
10 department_id NUMBER(4),
11 email VARCHAR2(25)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 DEFAULT DIRECTORY dir_test
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 badfile admin_bad_dir:'test.bad'
21 logfile admin_log_dir:'test.log'
22 fields terminated by ','
23 missing field values are null
24 ( employee_id, first_name, last_name, job_id, manager_id,
25 hire_date char date_format date mask "dd-mm-yyyy",
26 salary, commission_pct, department_id, email
27 )
28 )
29 LOCATION ('test.txt')
30 )
31 PARALLEL
32 REJECT LIMIT UNLIMITED;

表已创建。

SQL> select count(1) from test_ext;

COUNT(1)
----------
4

可以看到，已经可以从external table中查到数据。如果有问题，可以通过查看d:/test.log和d:/test.bad发现错误原因，已经未能进入external table的数据。如果要记录log和bad数据行，对对应的directiry需要有write权限。

上面建表语句中，允许使用并行来load数据，但是还需要在session或者system级允许并行
SQL> alter session enable parallel dml;

会话已更改。

2.利用ata pump驱动的external table导出数据到文件

data pump驱动的external需要基于data dump导出的文件，而不是普通的文本文件。同时使用data pump驱动的external可以将数据从数据库碇械汲龅轿募?/font>

SQL> CREATE TABLE test_ext2
2 ORGANIZATION EXTERNAL
3 (
4 TYPE oracle_datapump
5 DEFAULT DIRECTORY dir_test
6 LOCATION ('test.dump')
7 )
8 PARALLEL
9 as
10 select * from test_ext;

表已创建。

SQL> select count(1) from test_ext;

COUNT(1)
----------
4

注意d:/test.dmp文件不能已经存在，不然会报错
CREATE TABLE test_ext2
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-11012: 文件 test.dmp 已存在于 d:/ 中
ORA-06512: 在 "SYS.ORACLE_DATAPUMP", line 19

3.利用data pump驱动的external table读取dmp文件
SQL> CREATE TABLE test_ext3
2 (employee_id NUMBER(4),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 job_id VARCHAR2(10),
6 manager_id NUMBER(4),
7 hire_date DATE,
8 salary NUMBER(8,2),
9 commission_pct NUMBER(2,2),
10 department_id NUMBER(4),
11 email VARCHAR2(25)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE oracle_datapump
16 DEFAULT DIRECTORY dir_test
17 LOCATION ('test.dmp')
18 );

表已创建。

SQL> select count(1) from test_ext3;

COUNT(1)
----------
4