【转】外部表external table OCP--047-16/56

长枪不倒

　　外表(external table)就像普通的表对像一样，可以select等，只是它是只读的，数据库中只保存了表结构的描述，表数据却没有存放在数据库内，而是存放在了文件系统上。当用户想偶尔使用数据库外的结构化数据时，用起外表来就非常方便，甚至比sqlldr都要方便的多
　　外部表是在数据库以外的文件系统上存储的只读表，例如EXCEL、CSV等文件
　　定义
　　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.
　　u 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"
　　u 数据在数据库的外部组织，是操作系统文件。
　　u 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。        
　　u 数据是只读的。（外部表相当于一个只读的虚表）
　　u 不可以在上面运行任何 DML 操作，不可以创建索引。     
　　u 可以查询操作和连接。可以并行操作。
　　An external table is a read-only table that is defined within the database but exists outside of the database.
  --外部表是一个在数据库里定义的只读表，但是它存在于数据库的外部。
In more technical terms, the external table’s metadata is stored inside the database, and the data it contains is outside of the database.
  　　--用技术术语表达就是，外部表的元数据保存在数据库里，但是数据不在数据库里。
You can query them with the SELECT statement,but you cannot use any other DML statement on them.
  　　--可以select,但是不可以使用DML.
you can't create an INDEX on them,and they won't accept constraints.
  　　--不可以在外部表上创建索引index,也不允许添加约束
We specify that we are using ORACLE_LOADER, aka the SQL*Loader feature. An ALTERNATIVE type value here would be ORACLE_DATABUMP.
　　
　　
　　
　　建立外部表的步骤：
　　1、创建以“,”分隔的文件“TestTable.csv”至“D:\Test”
　　2、创建一个Directory：  



create directory TestTable_diras 'D:\Test' ;
　　3、创建一个外部表：   



create table TestTable(
ID varchar2 ( 10 ),
NAME varchar2 ( 20 ),
TYPE varchar2 ( 20 ),
AGEvarchar2 ( 20 ))
organization external (
type oracle_loader
default directory TestTable_dir
access parameters (fields terminatedby ',' )
location ( 'TestTable.csv' )
);
　　各类参数说明
　　1、type oracle_loader
　　数据转换驱动器，oracle_loader为默认，也可以改换其他
　　2、defaultdirectory TestTable_dir
　　location ('TestTable.csv')
　　指定外部表所在文件夹以及指定文件
　　3、accessparameters
　　设置转换参数，例如(fields terminatedby',')表示以','为字段间的分隔符
　　● 参数由访问驱动程序定义
　　
　　外部表的错误处理
　　1、REJECT LIMIT子句
　　在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。
　　* 默认的REJECT LIMIT值为0
　　* REJECT LIMIT UNLIMITED则不会报错
　　2、BADFILE 和 NOBADFILE 子句
　　在accessparameters中加入BADFILE'BAD_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD_FILE.txt'中
　　使用NOBADFILE子句则表示忽略转换错误的数据
　　● 如果不写BADFILE或NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD文件
　　● BADFILE只能记录前1次操作的结果，他会被第2次操作所覆盖。
　　3、LOGFILE 和 NOLOGFILE 子句
　　在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中
　　使用NOLOGFILE子句则表示不记录错误信息到log中
　　● 如果不写LOGFILE或NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG文件
　　
　　修改外部表语句
　　外部表与堆表一样可以之用ALTER TABLE命令修改表属性
　　* REJECT LIMIT        --错误数
　　* DEFAULT DIRECTORY   --默认目录
　　* ACCESS PARAMETERS   --参数
　　* LOCATION            --数据文件
　　* ADD COLUMN          --增加列
　　* MODIFY COLUMN       --列定义
　　* DROP COLUMN         --删除列
　　* RENAME TO           --外部表更名
　　外部表的一些限制：
# 不支持含有加密字段(Encrypted column)的导入与导出。
# 外部表不知道保存在数据库中的数据。
# 外部表不知道数据是如何在外部数据源中保存的。这是access parameter的功能。
# 外部表不能导入数据到LONG字段。
　　其他约束
　　    ● 外部表无法使用insert、update、delete等操作，要修改其数据只能通过修改数据文件。
　　    ● 外部表不能建立索引，如要建立，则需要先create table XX as select * from TestTable
　　PS:
　　1.外部表可以加载和卸载数据泵格式的数据，只需把organization external里的参数type设置为oracle_datapump。



create table all_objects_unload
organization external
(
type oracle_datapump
default directory testdir
location('allobjects.dat')
)
as
select * from all_objects
　　
　　2、例：用test创建一个external table emp_load



create table emp_load
　　(
　　　employee_number char(5),
　　employee_dob char(20),
　　employee_last_name char(20),
　　employee_first_name char(15),
　　employee_middle_name char(15),
　　employee_hire_date date
　　  )
organization external
　　(type oracle_loader
　　default directory def_dir
　　access parameters
　　(records delimited by newline
　　fields (
　　　　　　　　 employee_number char(2),
　　employee_dob char(20),
　　employee_last_name char(18),
　　employee_first_name char(11),
　　employee_middle_name char(11),
　　employee_hire_date char(10) date_format date mask "mm/dd/yyyy"
　　)
　　)
　　location ('info.dat')
);