oracle数据泵卸载

create or replace directory MY_DIR as '/home/oracle/backup';
grant read,write on directory MY_DIR to public;
drop directory MY_DIR;

-- Data Pump Unload

drop table all_objects_unload purge;
--将表卸载成文件
create table all_objects_unload
  organization external
  ( type oracle_datapump
    default directory MY_DIR
    location( 'allobjects.dat' )
  )
  as
  select
  *
  from all_objects
/

--获取表的结构
select dbms_metadata.get_ddl( 'TABLE', 'ALL_OBJECTS_UNLOAD' )
from dual;

--生成对应的外部表
CREATE TABLE "SCOTT"."ALL_OBJECTS_UNLOAD_TMP" 
   (    "OWNER" VARCHAR2(30), 
    "OBJECT_NAME" VARCHAR2(30), 
    "SUBOBJECT_NAME" VARCHAR2(30), 
    "OBJECT_ID" NUMBER, 
    "DATA_OBJECT_ID" NUMBER, 
    "OBJECT_TYPE" VARCHAR2(19), 
    "CREATED" DATE, 
    "LAST_DDL_TIME" DATE, 
    "TIMESTAMP" VARCHAR2(19), 
    "STATUS" VARCHAR2(7), 
    "TEMPORARY" VARCHAR2(1), 
    "GENERATED" VARCHAR2(1), 
    "SECONDARY" VARCHAR2(1), 
    "NAMESPACE" NUMBER, 
    "EDITION_NAME" VARCHAR2(30)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "MY_DIR"
     
      LOCATION
       ( 'allobjects.dat'
       )
    )
 


---把临时外部表的数据加入的数据库中
INSERT  /*+APPEND*/ INTO SOME_TABLE 
SELECT * FROM "SCOTT"."ALL_OBJECTS_UNLOAD_TMP"