外部表

一、向数据库加载CSV文件

使用外部表和SQL，可以向数据库加载小型或非常大的CSV文件。

下面是使用外部表访问OS CSV文件的步骤。

1.     创建指向OS CSV文件的数据库目录对象（directory）

2.     为创建外部表的用户分配目录对象的读写权限。

3.     运行CREATE TABLE …ORGANIZATION EXTERNAL语句

4.     使用sqlplus或plsql访问CSV文件的内容

二、操作示例

例子的文件名称为test0223.csv，位于/home/oracle目录中，文件内容为：

1.     创建目录对象

2.     授权，例子使用DBA用户，无需授权

如果是其他用户则需要

grant read,write on directory exa_dir to userxxx;

3.     创建外部表

Create table external_et(

  ext_id number,

  test_date date,

  remark varchar2(32)

)

ORGANIZATION EXTERNAL(

  type oracle_loader

  default DIRECTORY exa_dir

  access PARAMETERS(

    RECORDS delimited by newline

    fields terminated by '|'

    missing field values are null

    (ext_id,

    test_date CHAR date_format DATE mask "yyyymmdd",

    remark

  )

)

  LOCATION('test0223.csv')

)

REJECT limit UNLIMITED;

4.     查询表

5.     可查看外部表元数据

SELECT t.OWNER,t.table_name,t.default_directory_name,t.access_parameters FROM dba_external_tables t；

6.     使用外部表查看文本文件

1.例如使用外部表查看告警文件

SELECT value FROM v$diag_info where name='Diag Trace';

输出结果：

/u01/app/oracle/.../trace

2.创建目录对象

create or replace DIRECTORY t_loc as '/u01/app/oracle/.../trace';

3.创建外部表

create table alert_log_file1(

alert_text varchar2(4000))

ORGANIZATION EXTERNAL

( type oracle_loader

default DIRECTORY t_loc

access PARAMETERS(

RECORDS delimited by newline

nobadfile

nologfile

nodiscardfile

fields terminated by '#$~=ui$X'

missing field values are null

(alert_text)

)

LOCATION('alert_tradedb1.log')

)REJECT limit UNLIMITED;

7.     使用外部表卸载、加载数据（例子中目录对象 dp不再描述）

1.    原表 inv(

ID NUMBER,

DESC VARCHAR2(32)

)包含数据。

2.      Create table inv_et

 ORGANIZATION EXTERNAL

( type oracle_datapump

default DIRECTORY dp

LOCATION('inv.dmp')

As SELECT *  FROM  inv;

上面命令创建了两个事物：

一、根据INV表的结构和数据，创建了外部表INV_ET

二、生成跨平台的数据泵文件inv.dmp

这样可以将inv.dmp复制到其他数据库服务器，并根据该文件创建外部表

3.     Create table inv_dw

(

ID NUMBER,

DESC VARCHAR2(32)

)

 ORGANIZATION EXTERNAL

( type oracle_datapump

default DIRECTORY dp

LOCATION('inv.dmp');

这样就可以通过 SELECT * FROM inv_dw;访问数据