导入导出 Oracle 分区表数据

导入导出 Oracle 分区表数据-CUUG

--**************************** -- 导入导出 Oracle 分区表数据 --****************************

导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式，只不过导入导出需要考虑到分区的特殊性，如分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。下面将描述使用imp/exp，impdp/expdp导入导出分区表数据。 一、分区级别的导入导出

可以导出一个或多个分区，也可以导出所有分区(即整个表)。 可以导入所有分区(即整个表)，一个或多个分区以及子分区。

对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y，而使用impdp，加table_exists_action=append | replace 参数。 二、创建演示环境 1.查看当前数据库的版本

SQL> select * from v$version where rownum < 2; BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 2.创建一个分区表

SQL> alter session set nls_date_format='yyyy-mm-dd'; SQL> CREATE TABLE tb_pt ( sal_date DATE NOT NULL, sal_id NUMBER NOT NULL, sal_row NUMBER(12) NOT NULL)

1

partition by range(sal_date) (

partition sal_11 values less than(to_date('2012-01-01','YYYY-MM-DD')) , partition sal_12 values less than(to_date('2013-01-01','YYYY-MM-DD')) , partition sal_13 values less than(to_date('2014-01-01','YYYY-MM-DD')) , partition sal_14 values less than(to_date('2015-01-01','YYYY-MM-DD')) , partition sal_15 values less than(to_date('2016-01-01','YYYY-MM-DD')) , partition sal_16 values less than(to_date('2017-01-01','YYYY-MM-DD')) , partition sal_other values less than (maxvalue) ) nologging; 3.创建一个唯一索引

CREATE UNIQUE INDEX tb_pt_ind1 ON tb_pt(sal_date) nologging; 4.为分区表生成数据 SQL> INSERT INTO tb_pt

SELECT TRUNC(SYSDATE)+ROWNUM, dbms_random.random, ROWNUM FROM dual

CONNECT BY LEVEL<=5000;<> SQL> commit;

SQL> select count(1) from tb_pt partition(sal_11); COUNT(1) ---------- 300

SQL> select count(1) from tb_pt partition(sal_other); COUNT(1) ---------- 2873

2

SQL> select * from tb_pt partition(sal_12) where rownum < 3; SAL_DATE SAL_ID SAL_ROW --------- ---------- ---------- 01-JAN-12 -1.356E+09 301 02-JAN-12 -761530183 302

三、使用exp/imp导出导入分区表数据 1.导出整个分区表

[oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt.dmp' log='/u02/dmp/tb_pt.log' tables=tb_pt

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:18 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing o

Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table TB_PT

. . exporting partition SAL_11 300 rows exported . . exporting partition SAL_12 366 rows exported . . exporting partition SAL_13 365 rows exported . . exporting partition SAL_14 365 rows exported . . exporting partition SAL_15 365 rows exported . . exporting partition SAL_16 366 rows exported . . exporting partition SAL_OTHER 2873 rows exported

3