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


[每日一题] OCP1z0-047 :2013-07-27外部表――不能被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） 建目录
[html] 
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)创建外部表
[html] 
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
[html] 
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、  在外部表上建视图和同义词
[html] 
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、 对外部表建索引，不支操作
[html] 
gyj@OCM> create index idx_chaannel_id on ext_gyj_costs(channe