Using Oracle Data Pump to create an external table

一，Create an external table and generate dump file

1, Create a directory and assign related privilege(create any directory)

SQL> create directory test as '/home/oracle';

Directory created.

SQL> grant read,write on directory test to system;

Grant succeeded.

2,Create external table

SQL>create table emp_ext 
   organization external
  (type oracle_datapump
    default directory test
    location ('emp_ext.dump')
    )
  as select * from emp;

Table created.

这个时候就会在/home/oracle目录下生成一个emp_ext.dump的文件

注意

a,外部表是没有segment的，

SQL> select SEGMENT_NAME,SEGMENT_TYPE from user_segments where segment_name='EMP_EXT';

no rows selected

但是他在数据字典里是有信息的

SQL> select TABLE_NAME from user_tables where table_name='EMP_EXT';

TABLE_NAME
------------------------------
EMP_EXT

b,如果你把/home/oracle下的emp_ext.dump文件删除了，就查询不到了

SQL> select * from emp_ext;
select * from emp_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for load
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 19

这个时候你如果要是重新创建同样名字的外部表则需要删除原表

SQL> drop table emp_ext;

Table dropped.

 

二、Using the existing dump file to create an external table
1、把产生的dmp文件复制到其它主机上

[oracle@vmoel5u4 ~]$ scp emp_ext.dump 192.168.92.200:/home/oracle

2、创建directory，需要有 create any directory权限：

SQL> create directory test as '/home/oracle';

Directory created.

3、创建外部表：

SQL>CREATE TABLE emp_ext2
    (
        EMPLOYEE_ID NUMBER(6,0),
        FIRST_NAME VARCHAR2(20),
        LAST_NAME VARCHAR2(25) ,
        EMAIL VARCHAR2(25) ,
        PHONE_NUMBER VARCHAR2(20),
        HIRE_DATE DATE ,
        JOB_ID VARCHAR2(10) ,
        SALARY NUMBER(8,2),
        COMMISSION_PCT NUMBER(2,2),
        MANAGER_ID NUMBER(6,0),
        DEPARTMENT_ID NUMBER(4,0)
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY test
     LOCATION ('emp_ext.dump')
 20     );

Table created.

查询验证下：
SQL> select count(*) from emp_ext2;

  COUNT(*)
----------
       107

 

注意：外部表不能做DML，例如：

SQL> delete from emp_ext2;
delete from emp_ext2
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

SQL> update emp_ext2 set salary=salary*1.1;
update emp_ext2 set salary=salary*1.1
       *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

SQL> insert into emp_ext2 select * from emp_ext2;
insert into emp_ext2 select * from emp_ext2
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table