Oracle外部表

• 向数据库加载CSV文件

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

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

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

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

3. 运行CREATE TABLE …ORGANIZATION EXTERNAL语句

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

• 操作示例

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

1. 创建目录对象

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

如果是其他用户则需要

grantread,write on directory exa_dir to userxxx;

1. 创建外部表

Createtable external_et(

 ext_id number,

 test_date date,

 remark varchar2(32)

)

ORGANIZATIONEXTERNAL(

 type oracle_loader

 defaultDIRECTORYexa_dir

 accessPARAMETERS(

   RECORDS delimited bynewline

   fields terminated by'|'

   missing field valuesare null

   (ext_id,

   test_date CHARdate_format DATE mask"yyyymmdd",

   remark

 )

)

 LOCATION('test0223.csv')

)

REJECTlimitUNLIMITED;

1. 查询表

1. 可查看外部表元数据

SELECT t.OWNER,t.table_name,t.default_directory_name,t.access_parametersFROM dba_external_tablest；

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

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

SELECT value FROMv$diag_info wherename='DiagTrace';

输出结果：

/u01/app/oracle/diag/rdbms/tradedb/tradedb1/trace

1. 创建目录对象

createorreplaceDIRECTORYt_loc as'/u01/app/oracle/diag/rdbms/tradedb/tradedb1/trace';

1. 创建外部表

createtablealert_log_file1(

alert_textvarchar2(4000))

ORGANIZATIONEXTERNAL

(type oracle_loader

defaultDIRECTORYt_loc

accessPARAMETERS(

RECORDSdelimited by newline

nobadfile

nologfile

nodiscardfile

fieldsterminated by'#$~=ui$X'

missingfield values are null

(alert_text)

)

LOCATION('alert_tradedb1.log')

)REJECTlimitUNLIMITED;

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

2. 1.    原表inv(

IDNUMBER,

DESCVARCHAR2(32)

)包含数据。

1.  Createtable inv_et

 ORGANIZATIONEXTERNAL

(type oracle_datapump

defaultDIRECTORY dp

LOCATION('inv.dmp')

AsSELECT*  FROM  inv;

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

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

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

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

1. Createtable inv_dw

(

IDNUMBER,

DESCVARCHAR2(32)

)

 ORGANIZATIONEXTERNAL

(type oracle_datapump

defaultDIRECTORY dp

LOCATION('inv.dmp');

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