Oracle分区表数据的导入与导出_四海名汀

http://blog.sina.com.cn/u/1558925640

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

Oracle分区表数据的导入与导出

(2013-01-22 15:33:23)

标签：

oracle分区表导出

oracle分区表导入

分区表导入与导出

分区表知识

it

分类： Oracle数据库

Oracle分区表的使用在一定程度上提高了对表数据的存储及处理性能，在查询性能和数据存储安全上体现了其特有的优势，因此很好的利用分区表，在一定程度上有利于数据库性能的优化。而Oracle分区表数据的导入导出也成了这些工作中不可忽略的一部分。分区表的导入导出同普通表的导入导出方式没有什么很大区别，只不过在导入导出时需要考虑到分区的特殊性，如：分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。下面将描述使用imp/exp，impdp/expdp导入导出分区表数据。

有关分区表的特性请参考：

Oracle 分区表

SQL server 2005 切换分区表

SQL server 2005 基于已存在的表创建分区

有关导入导出工具请参考：

数据泵EXPDP 导出工具的使用

数据泵IMPDP 导入工具的使用

有关导入导出的官方文档请参考：

Original Export and Import

一、分区级别的导入导出

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(*)

---------------

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

连接到: 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

连接到: 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

连接到: 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

连接到: 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

连接到: 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(*)

--------

利用前面的备份数据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

连接到: 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

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：Oracle中Minus的用法

后一篇：Oracle中Decode函数使用技巧

新浪BLOG意见反馈留言板　欢迎批评指正