Oracle分区表的使用在一定程度上提高了对表数据的存储及处理性能，在查询性能和数据存储安全上体现了其特有的优势，因此很好的利用分区表，在一定程度上有利于数据库性能的优化。而Oracle分区表数据的导入导出也成了这些工作中不可忽略的一部分。分区表的导入导出同普通表的导入导出方式没有什么很大区别，只不过在导入导出时需要考虑到分区的特殊性，如：分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。下面将描述使用imp/exp，impdp/expdp导入导出分区表数据。
有关分区表的特性请参考：
有关导入导出工具请参考：
有关导入导出的官方文档请参考：
一、分区级别的导入导出
1、可以导出一个或多个分区，也可以导出所有分区(即整个表)。
2、可以导入所有分区(即整个表)，一个或多个分区以及子分区。
3、对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y；而使用impdp导入数据时，需要加table_exists_action=append。
4、相关参数的说明，ignore=y(表示忽略创建错误，继续后面的操作)，statistics=none(表示不接收统计信息)。
二、创建测试实例
1、查看当前Oracle数据库版本
select * from v$version where rownum<2
BANNER
-------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
2、创建一个分区表
create table test_pt
(
tid number primary key not null,
sdate date not null,
srow number(8) not null
)
partition by range(srow)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(maxvalue)
)nologging;
其中，tid为自增序列，需要创建对应的序列和触发器，其创建脚本如下：
创建序列脚本：
create sequence sq_test_pt
increment by 1 start with 1
maxvalue 1.0E28
minvalue 1
nocycle cache 20 order;
创建触发器脚本：
create or replace trigger tr_test_pt
before insert on test_pt for each row
begin
select sq_test.nextval into :new.tid from dual;
end;
3、为分区表插入数据
insert into test_pt(sdate,srow) select trunc(sysdate),rownum
from dual connect by level<=5000;
commit;
4、查询分区表数据
select count(*) from test_pt
COUNT(*)
--------------
5000
select count(*) from test_pt partition(p1)
COUNT(*)
---------------
99
select count(*) from test_pt partition(p3)
COUNT(*)
---------------
4801
select * from test_pt partition(p2) where rownum
TID SDATE SROW
----- --------------- -------
5720 2013-1-17 100
5721 2013-1-17 101
三、使用exp/imp进行数据导出、导入操作
1、导出整个分区表
在windows CMD命令下执行导出语句。
C:\Documents and Settings\Administrator>exp orcluser/orcl
file='c:\test_pt.dmp'
log='c:\test_pt.log' tables=test_pt
Export: Release 9.2.0.1.0 - Production on 星期二 1月 22 16:26:46
2013
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径 ...
. . 正在导出表 TEST_PT
. . 正在导出分区 P1 99 行被导出
. . 正在导出分区 P2 100 行被导出
. . 正在导出分区 P3 4801 行被导出
在没有警告的情况下成功终止导出。
2、导出单个(p1)分区
C:\Documents and Settings\Administrator>exp orcluser/orcl
file='c:\test_ptp1.dmp
' log='c:\test_ptp1.log' tables=test_pt:p1
statistics=none
Export: Release 9.2.0.1.0 - Production on 星期二 1月 22 16:30:32
2013
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径 ...
. . 正在导出表 TEST_PT
. . 正在导出分区 P1 99 行被导出
在没有警告的情况下成功终止导出。
3、导出多个分区(p1和p3区)数据
C:\Documents and Settings\Administrator>exp orcluser/orcl
file='c:\test_ptp13.dm
p' log='c:\test_ptp13.log' tables=(test_pt:p1,test_pt:p3)
statistics=none
Export: Release 9.2.0.1.0 - Production on 星期二 1月 22 16:34:17
2013
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径 ...
. . 正在导出表 TEST_PT
. . 正在导出分区 P1 99 行被导出
. . 正在导出分区 P3 4801 行被导出
在没有警告的情况下成功终止导出。
4、导入单个分区数据(使用之前备份的p1分区数据)
首先通过SQL语句删掉该分区(p1)的数据
alter table test_pt truncate partition p1;
然后再利用备份的数据test_ptp1.dmp实现恢复
C:\Documents and Settings\Administrator>imp orcluser/orcl
file='c:\test_ptp1.dmp
' tables=test_pt:p1 ignore=y
Import: Release 9.2.0.1.0 - Production on 星期二 1月 22 16:48:55
2013
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.1.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将ORCLUSER的对象导入到 ORCLUSER
. . 正在导入分区 "TEST_PT":"P1"
IMP-00058: 遇到 ORACLE 错误 1502
ORA-01502: 索引'ORCLUSER.SYS_C003020'或这类索引的分区处于不可用状态
成功终止导入，但出现警告。
注意：此时，出现错误“ORA-01502:
索引'ORCLUSER.SYS_C003020'或这类索引的分区处于不可用状态”的错误。提示此时的分区索引(此处为主键索引)处于脱机(offline)状态，需要将其修改为联机(online)状态。说明在对区数据进行截断时会导致索引脱机。
通过SQL语句执行：
alter index sys_c003020 rebuild
online;
此时再次执行上面的导入语句(成功导入数据)：
C:\Documents and Settings\Administrator>imp orcluser/orcl
file='c:\test_ptp1.dmp
' tables=test_pt:p1 ignore=y
Import: Release 9.2.0.1.0 - Production on 星期二 1月 22 16:54:31
2013
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.1.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将ORCLUSER的对象导入到 ORCLUSER
. . 正在导入分区 "TEST_PT":"P1" 99行被导入
成功终止导入，但出现警告。
5、导入整个分区表
首先通过SQL将表中的数据清空
TRUNCATE TABLE TEST_PT;
SELECT COUNT(*) FROM TEST_PT
COUNT(*)
--------
0
利用前面的备份数据test_pt.dmp实现数据恢复
C:\Documents and Settings\Administrator>imp orcluser/orcl
tables=test_pt file='c
:\test_pt.dmp' ignore=y
Import: Release 9.2.0.1.0 - Production on 星期二 1月 22 17:04:51
2013
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.1.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将ORCLUSER的对象导入到 ORCLUSER
. . 正在导入分区 "TEST_PT":"P1" 99行被导入
. . 正在导入分区 "TEST_PT":"P2" 100行被导入
. . 正在导入分区 "TEST_PT":"P3" 4801行被导入
成功终止导入，但出现警告。
本文参考资料：http://blog.csdn.net/robinson_0612/article/details/6246738
527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
