【SQL】外部表 External Table

目录

定义

外部表的创建

目录对象创建并赋权

使用DATAPUMP创建

使用ORACLE_LOADER创建外部表并导入数据

---

定义

External tables access data in external sources as if it were in a table in the database.

You can connect to the database and create metadata for the external table using DDL.

The DDL for an external table consists of two parts: one part that describes the Oracle

column types, and another part (the access parameters) that describes the mapping of

the external data to the Oracle data columns.

外部表是指不在库里的表，是操作系统上的一个按照一定格式分割的文本文件，或是其他类型的表，对oracle来说类似于视图，可以在数据库中像视图一样进行查询等操作，但是外部表在数据库中只有表结构，而数据存放在操作系统中。

外部表是通过如下语句创建，创建时需指定如下属性

create table TABLE_NAME …organization external

1、TYPE：指定外部表的类型，也就是驱动访问，于SQL*Loader不同的是访问驱动运行在服务器内部，有两种可用的类型

ORACLE_LOADER:默认的访问驱动，它可将数据从外部表加载到内部表，数据必须要来自文本数据文件，但他不能将数据从内部表卸载到外部表。

ORACLE_DATAPUMP:它可以执行数据的加载和卸载，数据必须来自二进制转储文件，从外部表加载到内部表时，是通过二进制转储文件中提取来完成的（相当于导入），而从内部表卸载到外部表是通过填充外部表的二进制转储文件来完成（相当于导出）。转储文件一旦创建，可以读取任意次数，但是不能被修改（不能对其进行DML操作）。

2、DEFAULT DIRECTORY：所有输入输出文件使用的默认目录，位置是一个目录对象，数据逻辑位置，而不是一个具体在操作系统上的路径。在使用外部表之前必须创建（同数据泵）

3、ACCESS PARAMETER：描述外部数据源，每一种类型的外部表都有自己的访问驱动程序，该驱动程序为该类型的外部表提供唯一的访问参数

4、LOACATION：制定外部表的数据文件，文件命名方式directory:file,若不指定directory，则使用默认目录

外部表的创建

目录对象创建并赋权

SQL> show user; USER is "SYS" SQL> ho mkdir /home/oracle/scott;   SQL> create directory ext_scott as'/home/oracle/scott';   Directory created.   SQL> grant read,write on directory ext_scott to scott;   Grant succeeded.

使用DATAPUMP创建

SQL> create table emp_ext01(no,name,work) organization external (   TYPE oracle_datapump   DEFAULT DIRECTORY ext_scott   LOCATION ('emp01.dmp','emp02.dmp') ) parallel as select empno,ename,job from emp where rownum<=5;   Table created.

查看外部表所创建的二进制文件

[oracle@oracle12c scott]$ cd /home/oracle/scott/ [oracle@oracle12c scott]$ ls -l total 36 -rw-r----- 1 oracle oinstall 12288 Jan  8 11:03 emp01.dmp -rw-r----- 1 oracle oinstall 12288 Jan  8 11:03 emp02.dmp -rw-r--r-- 1 oracle oinstall    41 Jan  8 11:03 EMP_EXT01_18493.log -rw-r--r-- 1 oracle oinstall    41 Jan  8 11:03 EMP_EXT01_18495.log -rw-r--r-- 1 oracle oinstall    41 Jan  8 11:03 EMP_EXT01_23629.log

定义其他的外部表，并从中读取文件数据。

SQL> create table emp_ext_scott(no number(4),name varchar2(10),work varchar2(9)) organization external ( TYPE oracle_datapump DEFAULT DIRECTORY ext_scott LOCATION ('emp01.dmp','emp02.dmp') );   Table created.   SQL> select * from emp_ext_scott;           NO NAME       WORK ---------- ---------- ---------       7369 SMITH      CLERK       7499 ALLEN      SALESMAN       7521 WARD       SALESMAN       7566 JONES      MANAGER       7654 MARTIN     SALESMAN

注意:外部表 emp_ext_scott 中的字段名、类型、长度，要和外部表中的一致

如果这个时候在造作系统层面把二进制文 件移走的话会怎么样呢？

[oracle@oracle12c scott]$ mkdir bak [oracle@oracle12c scott]$ mv *.dmp bak [oracle@oracle12c scott]$ sqlplus / as sysdba   SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 8 11:17:48 2021   Copyright (c) 1982, 2016, Oracle.  All rights reserved.     Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production   SQL> conn scott/tiger; Connected. SQL> select * from emp_ext_scott; select * from emp_ext_scott               * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-11010: unable to open at least one dump file for fetch   SQL> DESC EMP_EXT_SCOTT;    Name                                      Null?    Type  ----------------------------------------- -------- ----------------------  NO                                                 NUMBER(4)  NAME                                               VARCHAR2(10)  WORK                                               VARCHAR2(9)

当然是读不到数据了,只能看到表的定义，由此可以看出来数据并没有存到数据库中.

 

使用ORACLE_LOADER创建外部表并导入数据

创建测试数据

[oracle@oracle12c scott]$ cat ext_scott_oracle_loder.txt 11,name1,job1 12,name2,job2 13,name3,job3 14,name4,job4 15,name5,job5 16,name6,job6

创建外部表

SQL> create table emp_ext_loader (no number(3),name varchar2(10),job varchar2(10)) organization external(                       TYPE oracle_loader                       DEFAULT DIRECTORY ext_scott                       ACCESS PARAMETERS(                                         records delimited by newline                                         fields terminated by ','                                         missing field values are null                                         )                       LOCATION('ext_scott_oracle_loder.txt')                       );               Table created.   SQL> select * from emp_ext_loader;           NO NAME       JOB ---------- ---------- ----------         11 name1      job1         12 name2      job2         13 name3      job3         14 name4      job4         15 name5      job5         16 name6      job6   6 rows selected.

注意:

读取数据时，每行数据为 1 条记录

字段之间以逗号隔开

未赋值的字段为 null

如果再使坏，移走改文件又会是什么样呢？

[oracle@oracle12c scott]$ mv ext_scott_oracle_loder.txt bak   SQL> select * from emp_ext_scott; select * from emp_ext_scott               * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-11010: unable to open at least one dump file for fetch     SQL> desc  emp_ext_scott； SP2-0565: Illegal identifier. SQL> desc  emp_ext_scott； SP2-0565: Illegal identifier. SQL>  desc  emp_ext_scott;  Name                                      Null?    Type  ----------------------------------------- -------- -------------------------  NO                                                 NUMBER(4)  NAME                                               VARCHAR2(10)  WORK                                               VARCHAR2(9)

虽然看不到数据了，但是可以看见表结构。