数据泵参数partition_options在对于迁移分区表的使用。
1 NONE 象在系统上的分区表一样创建。各分区须对应或用remap_tablespace重新指定。
2 DEPARTITION 每个分区表和子分区表作为一个独立的表创建，名字使用表和分区（子分区）名字的组合。
3 MERGE 将所有分区合并到一个表，合并到主表
一 ：创建环境：
yang@rac1>create table yang_part2 (
id number,
create_time date,
value varchar2(20),
name varchar2(30)
)
partition by range (create_time) (
partition p2090 values less than (to_date('20100101','yyyymmdd')),
partition p201001 values less than (to_date('20100401','yyyymmdd')),
partition p201002 values less than (to_date('20100701','yyyymmdd')),
partition p201003 values less than (to_date('20101001','yyyymmdd')),
partition p201004 values less than (MAXVALUE)
);
表已创建yang@rac1>insert into yang_part2
select rownum id, sysdate - dbms_random.value(1,400) create_time,
dbms_random.string('s',15) value,
dbms_random.string('d',15) name
from dual
connect by level <=1e4;
已创建1000行yang@rac1>COMMIT;
提交完成。导出数据
C:\Users\aaaa>expdp yang/yang tables=yang_part2 directory=dumpdir dumpfile=yangpart2.dmp logfile=yangpart2.log
Export: Release 11.1.0.6.0 - Production on 星期三, 30 3月, 2011 22:25:47
Copyright (c) 2003, 2007, Oracle. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "YANG"."SYS_EXPORT_TABLE_01": yang/******** tables=yang_part2 directory=dumpdir dumpfile=yangp
art2.dmp logfile=yangpart2.log
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 832 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "YANG"."YANG_PART2":"P201004" 219.3 KB 4549 行
. . 导出了 "YANG"."YANG_PART2":"P201002" 111.1 KB 2239 行
. . 导出了 "YANG"."YANG_PART2":"P201003" 114.5 KB 2312 行
. . 导出了 "YANG"."YANG_PART2":"P201001" 48.36 KB 900 行
. . 导出了 "YANG"."YANG_PART2":"P2090" 0 KB 0 行
已成功加载/卸载了主表 "YANG"."SYS_EXPORT_TABLE_01"
yang@rac1>drop table yang_part2 purge;
******************************************************************************
YANG.SYS_EXPORT_TABLE_01 的转储文件集为:
D:\DUMP\YANGPART2.DMP
作业 "YANG"."SYS_EXPORT_TABLE_01" 已于 22:26:04 成功完成二：分别使用不同参数对分区表进行导入实验
1） partition_options=none模式
_PART2":"P201004" 219.3 KB 4549 行
. . 导入了 "YANG"."YANG_PART2":"P201002" 111.1 KB 2239 行
. . 导入了 "YANG"."YANG_PART2":"P201003" 114.5 KB 2312 行
. . 导入了 "YANG"."YANG_PARC:\Users\aaaa>impdp yang/yang directory=dumpdir dumpfile=yangpart2.dmp partition_options=none logfile=impdpyangpart2.log
Import: Release 11.1.0.6.0 - Production on 星期三, 30 3月, 2011 22:29:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "YANG"."SYS_IMPORT_FULL_01"
启动 "YANG"."SYS_IMPORT_FULL_01": yang/******** directory=dumpdir dumpfile=yangpart2.dmp partition_options=none
logfile=impdpyangpart2.log
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "YANG"."YANGT2":"P201001" 48.36 KB 900 行
. . 导入了 "YANG"."YANG_PART2":"P2090" 0 KB 0 行
作业 "YANG"."SYS_IMPORT_FULL_01" 已于 22:29:04 成功完成
yang@rac1>select dbms_metadata.get_ddl('TABLE','YANG_PART2') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','YANG_PART2')
--------------------------------------------------------------------------------
CREATE TABLE "YANG"."YANG_PART2"
( "ID" NUMBER,
"CREATE_TIME" DATE,
"VALUE" VARCHAR2(20),
"NAME" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("CREATE_TIME")
(PARTITION "P2090" VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI
:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P201001" VALUES LESS THAN (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_
CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P201002" VALUES LESS THAN (TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GR
EGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P201003" VALUES LESS THAN (TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P201004" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )--导入的表和以前一样。
yang@rac1>select table_name from user_tables;
TABLE_NAME
------------------------------
MLOG$_YANGTAB
YANGTAB2
MV_YANGTAB
MV_YANGTAB2
YANGALL_MV
DA_TEST
RUPD$_YANGTAB
YANG_PART2
YANGTAB
已选择9行。
yang@rac1>select count(1) from yang_part2;
COUNT(1)
----------
10000
yang@rac1>drop table yang_part2;
表已删除。
yang@rac1>purge recyclebin;
回收站已清空。2） partition_options=merge 模式
C:\Users\aaaa>impdp yang/yang directory=dumpdir dumpfile=yangpart2.dmp partition_options=merge logfile=impdpyangpart3.log
Import: Release 11.1.0.6.0 - Production on 星期三, 30 3月, 2011 22:32:02
Copyright (c) 2003, 2007, Oracle. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "YANG"."SYS_IMPORT_FULL_01"
启动 "YANG"."SYS_IMPORT_FULL_01": yang/******** directory=dumpdir dumpfile=yangpart2.dmp partition_options=merge
logfile=impdpyangpart3.log
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "YANG"."YANG_PART2":"P201004" 219.3 KB 4549 行
. . 导入了 "YANG"."YANG_PART2":"P201002" 111.1 KB 2239 行
. . 导入了 "YANG"."YANG_PART2":"P201003" 114.5 KB 2312 行
. . 导入了 "YANG"."YANG_PART2":"P201001" 48.36 KB 900 行
. . 导入了 "YANG"."YANG_PART2":"P2090" 0 KB 0 行
作业 "YANG"."SYS_IMPORT_FULL_01" 已于 22:32:05 成功完成yang@rac1>select count(1) from yang_part2;
COUNT(1)
----------
10000
yang@rac1>select dbms_metadata.get_ddl('TABLE','YANG_PART2') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','YANG_PART2')
--------------------------------------------------------------------------------
CREATE TABLE "YANG"."YANG_PART2"
( "ID" NUMBER,
"CREATE_TIME" DATE,
"VALUE" VARCHAR2(20),
"NAME" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"--导入的表被合成一个表了。
yang@rac1>drop table yang_part2 purge;
注意：如果导出时使用了TRANSPORTABLE参数，这里就不能使用NONE和MERGE3) partition_options=departition 模式
C:\Users\aaaa>impdp yang/yang directory=dumpdir dumpfile=yangpart2.dmp partition_options=departition logfile=impdpyangpart3.log
Import: Release 11.1.0.6.0 - Production on 星期三, 30 3月, 2011 22:33:20
Copyright (c) 2003, 2007, Oracle. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "YANG"."SYS_IMPORT_FULL_01"
启动 "YANG"."SYS_IMPORT_FULL_01": yang/******** directory=dumpdir dumpfile=yangpart2.dmp partition_options=departition
logfile=impdpyangpart3.log
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "YANG"."YANG_PART2_P201004" 219.3 KB 4549 行
. . 导入了 "YANG"."YANG_PART2_P201002" 111.1 KB 2239 行
. . 导入了 "YANG"."YANG_PART2_P201003" 114.5 KB 2312 行
. . 导入了 "YANG"."YANG_PART2_P201001" 48.36 KB 900 行
. . 导入了 "YANG"."YANG_PART2_P2090" 0 KB 0 行
作业 "YANG"."SYS_IMPORT_FULL_01" 已于 22:33:26 成功完成--在使用DEPARTITION选项导入后，Oracle采用默认命名方法TABLE_NAME + ‘_’ + PARTITION_NAME，将每个分区都创建为一张表。
yang@rac1>select table_name from user_tables;
TABLE_NAME
------------------------------
MLOG$_YANGTAB
YANGTAB2
MV_YANGTAB
MV_YANGTAB2
YANGALL_MV
DA_TEST
YANG_PART2_P201001
YANG_PART2_P201002
YANG_PART2_P201003
YANG_PART2_P201004
YANG_PART2_P2090
RUPD$_YANGTAB
YANGTAB
已选择13行。
yang@rac1>select count(1) from yang_part2_p2090;
COUNT(1)
----------
0
yang@rac1>select count(1) from yang_part2_p201001;
COUNT(1)
----------
900
yang@rac1>select count(1) from yang_part2_p201002;
COUNT(1)
----------
2239
yang@rac1>select count(1) from yang_part2_p201003;
COUNT(1)
----------
2312
yang@rac1>select count(1) from yang_part2_p201004;
COUNT(1)
----------
4549