ORACLE9I中外部表的使用

ORACLE9I中外部表的使用

说明: 这个帖子讲述Oracle9I外部表(External Table)的初级使用。


什么是外部表？

External table和正规的表很相似，具体的定义可以参见《Oracle概念手册》，以下的几点需要注意：

   >创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"
   >数据在数据库的外部组织，是操作系统文件。
   >操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。       
   >数据是只读的。（外部表相当于一个只读的虚表）
   >不可以在上面运行任何DML操作，不可以创建索引。   
   >可以查询操作和连接。可以并行操作。

例子：

假定有如下的两个数据文件：

1 平面文件数据的描述
  假设如下的两个平面文件
  1.dat：

   7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
   7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
   7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
   7566,JONES,MANAGER,7839,02-APR-81,1150,0,20


  2.dat：

   7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30
   7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30
   7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10


   （要有对操作系统中该目录的读写权限。）
    
2 创建一个逻辑目录并进行适当授权：
  

     SQL> CREATE DIRECTORY TESTDIR AS @#D:\TEMP\@#;
     目录已创建。

     SQL> GRANT READ ON DIRECTORY TESTDIR TO DEMO;
     授权成功。

     SQL> GRANT WRITE ON DIRECTORY TESTDIR TO DEMO;
     授权成功。


注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。

3 创建外部表

SQL> ED
已写入文件 afiedt.buf

  1 CREATE TABLE DEMO.EXT
  2 (emp_id number(4),
  3 ename varchar2(12),
  4 job varchar2(12) ,
  5 mgr_id number(4) ,
  6 hiredate date,
  7 salary number(8),
  8 comm number(8),
  9 dept_id number(2))
10 ORGANIZATION EXTERNAL
11 (TYPE ORACLE_LOADER
12 DEFAULT DIRECTORY TESTDIR
13 ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
14 FIELDS TERMINATED BY @#,@#)
15* LOCATION(@#1.DAT@#,@#2.DAT@#))
SQL> /

表已创建。


4 进行Select选择操作看看是否正确：
      

      SQL> select * from DEMO.EXT；

      EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
      ------ ------- ---------- ------- --------- -------- ---------- -------
        7369 SMITH CLERK 7902 17-DEC-80 100 0 20
        7499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30
        7521 WARD SALESMAN 7698 22-FEB-81 450 0 30
        7566 JONES MANAGER 7839 02-APR-81 1150 0 20
        7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
        7698 BLAKE MANAGER 7839 01-MAY-81 1550 0 30
        7934 MILLER CLERK 7782 23-JAN-82 3500 0 10




   如果要得到外部表的有关信息：


SQL> DESC DBA_EXTERNAL_TABLES;

名称
-----------------------------------------------------------------
OWNER
TABLE_NAME
TYPE_OWNER
TYPE_NAME
DEFAULT_DIRECTORY_OWNER
DEFAULT_DIRECTORY_NAME
REJECT_LIMIT
ACCESS_TYPE
ACCESS_PARAMETERS

SQL> SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERS
  2 FROM
  3 DBA_EXTERNAL_TABLES;

OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_PARAMETERS
------------------ ------------------------------ ------------------------------
DEMO EXT TESTDIR RECORDS DELIMITED BY NEWLINE
                                                                 FIELDS TERMINATED BY @#,@#


如果DBA想要知道平面文件的位置，使用如下的查询：


SQL> desc DBA_EXTERNAL_LOCATIONS;
名称
-----------------------------------------
OWNER
TABLE_NAME
LOCATION
DIRECTORY_OWNER
DIRECTORY_NAME

SQL> select * from DBA_EXTERNAL_LOCATIONS;

OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME
------------------------------------------------------------------------
DEMO EXT 1.DAT SYS TESTDIR
DEMO EXT 2.DAT SYS TESTDIR


参考文档：Oracle9i Database Administration Guide

原文转自：http://www.ltesting.net