数据泵卸载

来源：互联网发布：delphi 传奇源码编辑：程序博客网时间：2024/04/29 17:40
--数据泵卸载
用外部表把数据库中的数据卸载到文件系统。
--模拟实验数据泵卸载再在新环境中插入EODA@PROD1> create or replace directory tmp as '/tmp';Directory created.EODA@PROD1> set echo onEODA@PROD1> create table all_objects_unload  2    organization external  3    ( type oracle_datapump  --数据泵  4   default directory TMP  5   location( 'allobjects.dat' )  --定义路径  6    )  7    as  8    select  9    * 10    from all_objects 11  /Table created.EODA@PROD1> EODA@PROD1> select dbms_metadata.get_ddl( 'TABLE', 'ALL_OBJECTS_UNLOAD' )  --查看建表语句  2  from dual;DBMS_METADATA.GET_DDL('TABLE','ALL_OBJECTS_UNLOAD')--------------------------------------------------------------------------------  CREATE TABLE "EODA"."ALL_OBJECTS_UNLOAD"   ("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 "TMP"      LOCATION       ( 'allobjects.dat'       )    )[oracle@ocm1 ~]$ ll /tmp/allobjects.dat -rw-r----- 1 oracle oinstall 7397376 Dec  8 01:00 /tmp/allobjects.dat--将allobjects.dat复制到目标服务器--开始建表SCOTT@PROD1>   CREATE TABLE "SCOTT"."ALL_OBJECTS_UNLOAD"  2  (    "OWNER" VARCHAR2(30),  3       "OBJECT_NAME" VARCHAR2(30),  4       "SUBOBJECT_NAME" VARCHAR2(30),  5       "OBJECT_ID" NUMBER,  6       "DATA_OBJECT_ID" NUMBER,  7       "OBJECT_TYPE" VARCHAR2(19),  8       "CREATED" DATE,  9       "LAST_DDL_TIME" DATE, 10       "TIMESTAMP" VARCHAR2(19), 11       "STATUS" VARCHAR2(7), 12       "TEMPORARY" VARCHAR2(1), 13       "GENERATED" VARCHAR2(1), 14       "SECONDARY" VARCHAR2(1), 15       "NAMESPACE" NUMBER, 16       "EDITION_NAME" VARCHAR2(30) 17  ) 18  ORGANIZATION EXTERNAL 19   ( TYPE ORACLE_DATAPUMP 20     DEFAULT DIRECTORY "TMP" 21     LOCATION 22      ( 'allobjects.dat' 23      ) 24   ) 25  ;Table created.SCOTT@PROD1> insert /*+ append */ into new_tb as sekect * from all_objects_unload --通过此句将外部表的数据加载到本地数据库SCOTT@PROD1> select count(*) from all_objects_unload;  COUNT(*)----------     72936
0 0