Using Oracle Data Pump to create an external table


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