Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【SQL】ORACLE外部表

16 Apr 2019 » SQL

Oracle外部表

0.概述

外部表在Oracle 9i版本之后引用。外部表,是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据,我们可以把一个操作系统文件当成一个只读的数据库表,就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。

1.特性

  • 只读,不能使用DML语句,不能建索引。
  • 不能用ANALYZE语句对外部表做统计数据,可使用DMBS_STATS来采集外部表的统计数据。

2.使用ORACLE_DATAPUMP创建外部表

2.1 创建目录

[oracle@db ~]$ mkdir -p /home/oracle/data/
[oracle@db ~]$ sqlplus "/as sysdba"
SQL> create or replace directory zsd_dir as '/home/oracle/data/';
SQL> grant read,write on directory zsd_dir to scott;

2.2 创建外部表

create table ex_table2
(ename,job,sal,dname)
organization external
(
type oracle_datapump 
default directory zsd_dir 
location('ex_table2.dmp')
)
parallel 1
as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;

2.3 导入上述外部表

SQL> !cp /home/oracle/data/ex_table2.dmp /home/oracle/data/ex_table_temp.dmp

create table in_temp1
(ename varchar2(10),job varchar2(9),sal number(7,2),dname varchar(14))
organization external
(
type oracle_datapump 
default directory zsd_dir 
location('ex_table_temp.dmp')
);

2.4 外部表转化成正式的数据库表

create table zsd_tb1 as select * from in_temp1;

3.使用外部文件数据,创建Oracle外部表

使用oracle_loader对数据进行装载。

3.1 创建外部文件

vi /home/oracle/data/1.log
"7369","SMITH","CLERK","7902","17-DEC-80","100","0","20"
"7499","ALLEN","SALESMAN","7698","20-FEB-81","250","0","30"
"7521","WARD","SALESMAN","7698","22-FEB-81","450","0","30"
"7566","JONES","MANAGER","7839","02-APR-81","1150","0","20"

vi /home/oracle/data/2.log
"7654","MARTIN","SALESMAN","7698","28-SEP-81","1250","0","30"
"7698","BLAKE","MANAGER","7839","01-MAY-81","1550","0","30"
"7934","MILLER","CLERK","7782","23-JAN-82","3500","0","10"

3.2 创建外部表

create table emp_zsd(
                    emp_id number(4),
                    ename varchar2(15),
                    job varchar2(12),
                    mgr_id number(4),
                    hiredate date,
                    salary number(8),
                    comm number(8),
                    dept_id number(2)
                    )
            organization external
                    (
                    type oracle_loader
                    default directory zsd_dir
                    access parameters(
                                    records delimited by newline
                                    badfile 'emp_zsd%a_%p.bad'
                                    logfile 'emp_zsd%a_%p.log'
                                    fields terminated by ','
                                    optionally enclosed by '"'
                                    lrtrim missing field values are null
                                    reject rows with all null fields
                                    )
                    location ('1.log','2.log')
)

4.外部表的一些视图

user_external_locations相关如下:

SQL> col TABLE_NAME format a30;
SQL> col LOCATION format a30;
SQL> col DIRECTORY format a30;
SQL> col DIRECTORY_NAME format a30;
SQL> select * from user_external_locations order by table_name;

TABLE_NAME                     LOCATION                       DIRECTORY DIRECTORY_NAME
------------------------------ ------------------------------ --------- ------------------------------
EMP_ZSD                        2.log                          SYS       ZSD_DIR
EMP_ZSD                        1.log                          SYS       ZSD_DIR

user_external_tables相关内容如下:

SQL> col TABLE_NAME format a20
SQL> col TYPE_NAME format a20
SQL> col DEFAULT_DIRECTORY_NAME format a20
SQL> col REJECT_LIMIT format a20
SQL> col ACCESS_PARAMETERS format a30
SQL> select TABLE_NAME,TYPE_NAME,DEFAULT_DIRECTORY_NAME,REJECT_LIMIT,ACCESS_PARAMETERS from user_external_tables;