[每日一题] OCP1z0-047 :2013-07-27 外部表――不能被DML和建索引

Oracle外部表详解

最新推荐文章于 2025-09-13 19:07:06 发布

转载最新推荐文章于 2025-09-13 19:07:06 发布 · 80 阅读

文章标签：

#数据库

本文介绍了Oracle数据库中外部表的功能及使用限制。通过实例演示了如何创建外部表，并对其进行了查询、试图创建等操作，同时也展示了外部表不支持的DML操作及索引建立等特性。

首先看官方文档上的解释：

Managing External Tables

Oracle Database allows you read-only access to data in external tables. External tables are definedas tables that do not reside in the database, and can be in any format forwhich an access driver is provided. By providing the database with metadatadescribing an external table, the database is able to expose the data in theexternal table as if it were data residing in a regular database table. Theexternal data can be queried directly and in parallel using SQL.

You can, for example, select, join, or sort externaltable data. You can also create views and synonyms for external tables.However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.

External tables also provide a framework to unload theresult of an arbitrary SELECT statement into aplatform-independent Oracle-proprietary format that can be used by Oracle DataPump.

实验测试：

1、创建一个外部表

（1）元数据：

[oracle@mydb ~]$more prod_master.dat

6,6,6,6,6,600

7,7,7,7,7,700

（2）建目录

sys@OCM> create directory dir_gyj as '/home/oracle';

Directory created.

sys@OCM> grant read,write on directory dir_gyj to gyj;

Grant succeeded.

(3)创建外部表

gyj@OCM> CREATE TABLE ext_gyj_costs 
  2  (
  3    "CHANNEL_ID" NUMBER,
  4    "PROD_ID" NUMBER,
  5    "PROMO_ID" NUMBER,
  6    "TIME_ID" NUMBER,
  7    "UNIT_COST" NUMBER,
  8    "UNIT_PRICE" NUMBER
  9  )
 10  ORGANIZATION external 
 11  (
 12    TYPE oracle_loader
 13    DEFAULT DIRECTORY dir_gyj
 14    ACCESS PARAMETERS 
 15    (
 16      FIELDS TERMINATED BY "," LDRTRIM 
 17      REJECT ROWS WITH ALL NULL FIELDS 
 18      (
 19        "CHANNEL_ID" CHAR(255)
 20          TERMINATED BY ",",
 21        "PROD_ID" CHAR(255)
 22          TERMINATED BY ",",
 23        "PROMO_ID" CHAR(255)
 24          TERMINATED BY ",",
 25        "TIME_ID" CHAR(255)
 26          TERMINATED BY ",",
 27        "UNIT_COST" CHAR(255)
 28          TERMINATED BY ",",
 29        "UNIT_PRICE" CHAR(255)
 30          TERMINATED BY ","
 31      )
 32    )
 33    location 
 34    (
 35      'prod_master.dat'
 36    )
 37  )REJECT LIMIT UNLIMITED;

Table created.

gyj@OCM> select * from ext_gyj_costs;

CHANNEL_ID    PROD_ID   PROMO_ID    TIME_ID  UNIT_COST UNIT_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         6          6          6          6          6        600
         7          7          7          7          7        700

2、对外部表做DML,操作如下，说明外部表不支持DML

gyj@OCM> Update  ext_gyj_costs set UNIT_PRICE=800 where channel_id=6;
Update  ext_gyj_costs set UNIT_PRICE=800 where channel_id=6
        *
ERROR at line 1:
ORA-30657: operation not supported on external organized table


gyj@OCM> delete from ext_gyj_costs where channel_id=6;
delete from ext_gyj_costs where channel_id=6
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

3、在外部表上建视图和同义词

gyj@OCM> Create view v_cost as select * from ext_gyj_costs;

View created.

gyj@OCM> select * from v_cost;

CHANNEL_ID    PROD_ID   PROMO_ID    TIME_ID  UNIT_COST UNIT_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         6          6          6          6          6        600
         7          7          7          7          7        700

gyj@OCM> create synonym s_cost for ext_gyj_costs;

Synonym created.

gyj@OCM> select * from s_cost;

CHANNEL_ID    PROD_ID   PROMO_ID    TIME_ID  UNIT_COST UNIT_PRICE
---------- ---------- ---------- ---------- ---------- ----------
         6          6          6          6          6        600
         7          7          7          7          7        700

4、对外部表建索引，不支操作

gyj@OCM> create index idx_chaannel_id on ext_gyj_costs(channel_id);
create index idx_chaannel_id on ext_gyj_costs(channel_id)
                                *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

总结：外部表除了可以select(查询)、建视图，同义词，其它操作如DML,建索引等都不支持。

正确答案：C