数据泵卸载

最新推荐文章于 2025-07-17 19:57:39 发布

blockheading

最新推荐文章于 2025-07-17 19:57:39 发布

阅读量449

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle import export 文章标签：数据

本文链接：https://blog.youkuaiyun.com/u013169075/article/details/53525248

Oracle import export 专栏收录该内容

8 篇文章

订阅专栏

本文介绍如何利用外部表功能将数据库中的数据高效地卸载到文件系统，实现数据迁移或备份。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--数据泵卸载

用外部表把数据库中的数据卸载到文件系统。

--模拟实验数据泵卸载再在新环境中插入
EODA@PROD1> create or replace directory tmp as '/tmp';

Directory created.

EODA@PROD1> set echo on
EODA@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