数据泵卸载

Oracle9i引入了外部表,作为向数据库中读取数据的一种方法.Oracle 10g则从另一个方向引入了这个特性,可以使用CREATE TABLE语句创建外部数据,从而由数据库卸载数据.从Oracle 10g起,这个数据从一种专用二进制格式抽取,这种格式称为数据 泵格式(Data Pump format),Oracle提供的EXPDP和IMPDP工具将数据从一个数据库移动另一个数据库所用的就是这种格式.

使用外部表卸载确实相当容易,就像使用CREATE TABLE AS SELECT语句一样简单.首先,需要一个DIRECTORY对象:

jy@JINGYONG> create or replace directory dir1 as '/home/oracle';目录已创建。

现在,准备使用一个简单的SELECT语句向这个目录中卸载数据,例如:

jy@JINGYONG> create table my_object_unload  2  organization external  3  (  4  type oracle_datapump  5  default directory DIR1  6  location('myobjects.dat')  7  )  8  as  9  select * from all_objects;表已创建。[oracle@jy ~]$ ls -lrt myobjects.dat-rw-r----- 1 oracle oinstall 4923392 Oct  5 04:09 myobjects.dat

从上面的信息可知已经生成了myobjects.dat文件

获取外部表my_object_unload的创建语句

SQL> select dbms_metadata.get_ddl( 'TABLE', 'MY_OBJECT_UNLOAD' ) from dual;DBMS_METADATA.GET_DDL('TABLE',--------------------------------------------------------------------------------  CREATE TABLE "JY"."MY_OBJECT_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)   )   ORGANIZATION EXTERNAL    ( TYPE ORACLE_DATAPUMP      DEFAULT DIRECTORY "DIR1"      LOCATION       ( 'myobjects.dat'       )    )

现在把生成的myobjects.dat文件拷贝到你要加载数据的服务器上创建目录(directory)并在要加载数据的用户下使用上面创建外部表的语句(记得要修改DEFAULT DIRECTORY "DIR1"指向你所存放myobjects.dat的目录)来创建外部表

CREATE TABLE "MY_OBJECT_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)   )   ORGANIZATION EXTERNAL    ( TYPE ORACLE_DATAPUMP      DEFAULT DIRECTORY "DIR1"      LOCATION       ( 'myobjects.dat'       )    );/SQL> select * from my_object_unload ;OWNER   OBJECT_NAME            SUBOBJECT_NAME  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED      LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY------- ---------------------- -------------- ---------- -------------- ------------------- ----------- ------- ------ ------------------- ------- --------- --------- ---------SYS     ICOL$                                         20              2 TABLE               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     I_USER1                                       44             44 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     CON$                                          28             28 TABLE               2005-6-30 1 2013-3- 24 12: 2005-06-30:19:10:16 VALID   N         N         NSYS     UNDO$                                         15             15 TABLE               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     C_COBJ#                                       29             29 CLUSTER             2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     I_OBJ#                                         3              3 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     PROXY_ROLE_DATA$                              25             25 TABLE               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     I_IND1                                        39             39 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     I_CDEF2                                       51             51 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         NSYS     I_PROXY_ROLE_DATA$_1                          26             26 INDEX               2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID   N         N         N

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