ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于External Table

关于External Table

原创 Linux操作系统 作者:NinGoo 时间:2019-07-16 08:45:02 0 删除 编辑

从oracle9i开始,通过External table,可以直接以表格的形式访问外部文件,而不需要事先通过sqlldr将文件load进数据库。oracle9i的external table是通过sqlldr引擎实现的。到了oracle10g,又增加了data pump方式的external table,该方式不但可以读取外部文件,甚至可以将数据从数据库写unload到外部文件中。通过dba_external_tables/all_external_tables/user_external_tables可以查看系统中的external tables的信息。


实验环境

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50248/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    131820