Oracle数据库外部表实验过程

1

使用datadump提供外部表的定义并创建外部表

a.创建系统目录以及Oracle数据目录名来建立对应关系，同时授予权限
[oracle@oradb ~]$ mkdir –p home/oracle/external_tb/data

SQL> create or replace directory dat_dir as '/home/oracle/external_tb/data/';

SQL> grant read,write on directory dat_dir to scott;
SQL> alter user scott account unlock identified by scott; 
b.创建外部表

SQL> conn scott/scott

Connected.

SQL> create table ex_tb1  

    (ename,job,sal,dname)    

    organization external

        (

        type oracle_datapump   

        default directory dat_dir  

    location('tb1.exp，tb2.exp'))

    parallel 2                     

    as                                  

   select ename,job,sal,dname

   from emp join dept

   on emp.deptno=dept.deptno ;

Table created.

SQL> select * from ex_tb1;

ENAME      JOB              SAL DNAME

---------- --------- ---------- --------------

SMITH      CLERK            800 RESEARCH

ALLEN      SALESMAN        1600 SALES

WARD       SALESMAN        1250 SALES

JONES      MANAGER         2975 RESEARCH

MARTIN     SALESMAN        1250 SALES

BLAKE      MANAGER         2850 SALES

CLARK      MANAGER         2450 ACCOUNTING

SCOTT      ANALYST         3000 RESEARCH

KING       PRESIDENT       5000 ACCOUNTING

TURNER     SALESMAN        1500 SALES

ADAMS      CLERK           1100 RESEARCH

ENAME      JOB              SAL DNAME

---------- --------- ---------- --------------

JAMES      CLERK            950 SALES

FORD       ANALYST         3000 RESEARCH

MILLER     CLERK           1300 ACCOUNTING

14 rows .
c.验证外部表
 SQL> select * from ex_tb1;
ENAME      JOB              SAL DNAME
---------- --------- ---------- --------------
SMITH      CLERK            800 RESEARCH
ALLEN      SALESMAN        1600 SALES
..................................
MILLER     CLERK           1300 ACCOUNTING 
对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表，用于转移数据。

2

使用平面文件定义并生成外部表

a.平面文件数据
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

$ pwd
home/oracle/external_tb/data
$ ls
1.dat  2.dat  dat_dir:tb_test.exp  EMP_NEW_3198.log  EMP_NEW_3413.log  EX_TB1_3021.log

创建外部表

create table emp_new
   (
       emp_id number(4),
       ename varchar2(15),
       job varchar2(12) ,
       mgr_id number(4) ,
       hiredate date,
       salary number(8),
       comm number(8),
       dept_id number(2)
   )
   organization external
   (
       type oracle_loader
       default directory dat_dir
       access parameters
     (
         records delimited by newline
         fields terminated by ','
     )
   location
       ('1.dat','2.dat')
  );
验证外部表 

SQL> select * from emp_new;
    EMP_ID ENAME           JOB              MGR_ID HIREDATE      SALARY       COMM    DEPT_ID
---------- --------------- ------------ ---------- --------- ---------- ---------- ----------
      7654 MARTIN          SALESMAN           7698 28-SEP-81       1250          0         30
.................................................................... 
外部表不能执行DML

SQL> delete from emp_new;
delete from emp_new
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table
查看外部表信息

SQL>select owner,table_name,type_name,default_directory_name,access_parameters
       2 from dba_external_tables;

获得平面文件的位置，使用如下的查询：

SQL>select * from dba_external_locations order by table_name;