ITPub博客

首页 > 数据库 > Oracle > Using Oracle Data Pump to create an external table

Using Oracle Data Pump to create an external table

Oracle 作者:地铁上小虫 时间:2015-11-03 13:53:59 0 删除 编辑

一,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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29653765/viewspace-1821509/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle SQL*Loader
请登录后发表评论 登录
全部评论

注册时间:2014-05-12

  • 博文量
    49
  • 访问量
    155488