这道题目的知识点是要你熟悉外部表，怎么建外部表，外部表的数据是怎么存储的等等。请给出正确答案，并解释A B C D每项，最好用实验测试证明！

 

外部表的metadata(元数据)是存在数据库中，但它的数据是存储在数据库外部的操作系统文件，oracle 可以以只读的方式来访问该文件，也就是说，可以在查询语句中将 External Table 与数据库中其他表进行连接（Join），但是不能给 External Table 加上索引，并且不能插入/更新/删除数据，不能添加虚拟列，因为它不是真正的数据库表。

 

A是错误的

The defaultrejection limit is zero

参考：SQL Language Reference

REJECT LIMIT Thisclause lets you specify an integer as an upper limit for the

number of errors tobe logged before the statement terminates and rolls back any

changes made by thestatement. The default rejection limit is zero. For parallel DML

operations, thereject limit is applied to each parallel server.

B是错误的

外部表的元数据是在数据库里面的，数据是存储在外部的

参考：Concept

external table

A read-only tablewhose metadata is stored in the database but whose data in stored in filesoutside the database. The database uses the metadata describing external tablesto expose their data as if they were relational tables.

C是错误的

ORACLE_LOADER是从外部表转载数据入数据库表

ORACLE_DATAPUMP是从数据库表卸载数据入外部表

2个驱动的功能是相反的

参考：Concept

About ExternalTables

Oracle Databaseprovides two access drivers for external tables. The default access driver isORACLE_LOADER, which allows the reading of data from external files using theOracle loader technology. The ORACLE_LOADER access driver provides data mappingcapabilities which are a subset of the control file syntax of SQL*Loaderutility. The second access driver, ORACLE_DATAPUMP, lets you unload data—thatis, read data from the database and insert it into an external table,represented by one or more external files—and then reload it into an OracleDatabase.

D是正确的

外部表可以用CREATE TABLE AS SELECT with the ORACLE_DATAPUMP access driver来创建

参考：Concept

External TableCreation

External tables areread-only unless created with CREATE TABLE AS SELECT with the ORACLE_DATAPUMPaccess driver. Restrictions for external tables include no support for indexedcolumns, virtual columns, and column objects.

 

 

 

创建外部表及卸载、加载数据操作

(1)、Loader生成外部表加载文件：Administrator's Guide 15章结尾 create external table

In the scriptsdirectory, you will find prod_master.dat and prod_master.ctl. Using theinformation found in these files, 

create an externaltable names PROD_MASTER in the SH schema of the PROD database.

[oracle@rptdb1external]$ cat prod_master.ctl

load data

infile'/home/oracle/dir/prod_master.dat'

append

into table costs

fields terminatedby ","

(channel_id,prod_id,promo_id,time_id,unit_cost,unit_price)

[oracle@rptdb1external]$

[oracle@rptdb1external]$ cat prod_master.dat

6,6,6,6,6,600

7,7,7,7,7,700 

create directorydir as '/opt/oracrs/external';

grant read,write ondirectory dir to sh;

create tablecosts(channel_id number,prod_id number,promo_id number,time_id number,unit_costnumber,unit_price number

);

sqlldr gyj/gyjcontrol=prod_master.ctl external_table=GENERATE_ONLY

(2)、Datapump外部表卸载：DatabaseData Warehousing Guide 12章结尾部分Extractinginto Export Files Using External Tables

CREATE TABLE ext_t1

ORGANIZATIONexternal

(

  TYPEORACLE_DATAPUMP

  DEFAULTDIRECTORY dump_file_dir

  location('t1.dat')

)

as SELECT * FROM T1WHERE ID=1;

(3)、Datapump外部表加载

SQL> createtable ext_t1

(id number,

name varchar2(100)

)

organizationexternal

(

type oracle_datapump

default directoryDUMP_FILE_DIR

location ('t1.dat')

);