oracle删除外部表数据,使用oracle外部表进行数据泵卸载数据

数据泵卸载

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 N

SYS I_USER1 44 44 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N

SYS CON$ 28 28 TABLE 2005-6-30 1 2013-3- 24 12: 2005-06-30:19:10:16 VALID N N N

SYS UNDO$ 15 15 TABLE 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N

SYS C_COBJ# 29 29 CLUSTER 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N

SYS I_OBJ# 3 3 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N

SYS PROXY_ROLE_DATA$ 25 25 TABLE 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N

SYS I_IND1 39 39 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N

SYS I_CDEF2 51 51 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N

SYS 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