Oracle 数据泵导入导出分区表及避坑指南
分区表的导入导出
首先根据emp表创建一个分区表，emp表如下：
SCOTT@dborcl1> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SCOTT@dborcl1> desc emp;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@dborcl1> select * from emp order by hiredate,deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
创建分区表，此分区表使用range-list组合分区，使用hiredate列做自增range分区，deptno列做list分区。
create table ptb partition by range(hiredate) interval(numtoyminterval(1,'year')) subpartition by list(deptno)
(
partition p1980 values less than(to_date('1980-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace users
(
subpartition p1 values(10) tablespace users,
subpartition p2 values(20) tablespace users,
subpartition p3 values(30) tablespace users
),
partition p1981 values less than(to_date('1981-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace users
(
subpartition p4 values(10) tablespace users,
subpartition p5 values(20) tablespace users,
subpartition p6 values(30) tablespace users
),
partition p1982 values less than(to_date('1982-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace users
(
subpartition p7 values(10) tablespace users,
subpartition p8 values(20) tablespace users,
subpartition p9 values(30) tablespace users
),
partition p1987 values less than(to_date('1987-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace users
(
subpartition p10 values(10) tablespace users,
subpartition p11 values(20) tablespace users,
subpartition p12 values(30) tablespace users
)
)
as select * from emp;
查看一下分区表的分区信息：
SCOTT@dborcl1> select table_name,partitioning_type,subpartitioning_type,status from user_part_tables;
TABLE_NAME PARTITION SUBPARTIT STATUS
------------------------------ --------- --------- --------
PTB RANGE LIST VALID
SCOTT@dborcl1> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
PTB P1980 P3 USERS
PTB P1980 P2 USERS
PTB P1980 P1 USERS
PTB P1981 P6 USERS
PTB P1981 P5 USERS
PTB P1981 P4 USERS
PTB P1982 P9 USERS
PTB P1982 P8 USERS
PTB P1982 P7 USERS
PTB P1987 P12 USERS
PTB P1987 P11 USERS
PTB P1987 P10 USERS
12 rows selected.
查看分区表的各个分区数据：
SCOTT@dborcl1> select * from ptb;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
开始导出（以下命令均经过实验验证）：
-
导出/导入整个分区表
expdp \' / as sysdba\' directory=expdp_dmp dumpfile=ptb.dmp nologfile=yes cluster=no tables=scott.ptb job_name=exp_ptb impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=ptb.dmp cluster=no nologfile=yes transform=oid:n remap_table=ptb:ptb_full \ tables=scott.ptb #在我的案例中，导入时可以不加tables=scott.ptb，因为导出时已经过滤了一遍。后面两个实验同理。 -
导出某个（些）分区
expdp \' / as sysdba\' directory=expdp_dmp dumpfile=ptb_p1980.dmp nologfile=yes cluster=no \ tables=scott.ptb:p1980 job_name=exp_ptbexpdp \' / as sysdba\' directory=expdp_dmp dumpfile=ptb_p19801981.dmp nologfile=yes cluster=no \ tables=scott.ptb:p1980,scott.ptb:p1981 job_name=exp_ptb impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=ptb_p19801981.dmp remap_table=ptb:ptb_19801981 cluster=no nologfile=yes \ transform=oid:n job_name=imp_ptb tables=scott.ptb:p1980,scott.ptb:p1981 -
导出某个（些）子分区
expdp \'/ as sysdba\' directory=expdp_dmp dumpfile=ptb_sub_p1p4p11.dmp nologfile=yes cluster=no job_name=exp_ptb \ tables=scott.ptb:p1,scott.ptb:p4,scott.ptb:p11 impdp \'/ as sysdba\' directory=expdp_dmp dumpfile=ptb_sub_p1p4p11.dmp nologfile=yes remap_table=ptb:ptb_sub_p1p4p11 cluster=no \ transform=oid:n job_name=imp_ptb tables=scott.ptb:p1,scott.ptb:p4,scott.ptb:p11
避坑指南
-
分区表导入时，如果指定了content=data_only，那么必须要在目标库存在分区表的表结构。可以选择自己创表结构，但是要保证创建正确，否则导入时一些数据无法导入，会报
ora 14400: inserted partition key does not map to any partition错误。因此，建议导出和导入时都不要加content参数，让oracle自己创建表结构。
-
remap_tablespace可以重定义分区表的所属表空间，包括分区表本身，分区，子分区。
-
如果在导出时只导出某个分区/子分区，导出时不加上content=data_only，导入时也不加content=data_only。那么导入之后，整个分区表的结构都是在的，也就是说所有分区都存在，只是导入时指定的分区有数据，而且他分区是没数据的。
-
另外记录一下某次数据迁移的merge问题，客户王哥在导出时只导出了分区表的数据，没有导出表结构，表结构是他自己创建的，而且创建之后，他还插入了一些数据。可是，导入的时候虽然加上了table_exists_action=append，可是报错ora 14400，因为表结构建的有问题。这种时候怎么办？
我是这么解决的，首先小王自己创建的表重命名一下，然后再次导入。之后使用merge来合并两张表的数据。为什么这么做呢？因为小王创建的表，字段和源库的表是一样的，只是分区没建好而已。因此可以使用merge，将小王新增的数据合并到导入之后的表中。但是却遇到了问题，merge语句执行的时候总是报错说缺少关键字。百度才查到，原来merge语句中，when matched then语句后面要跟update或delete，不能跟insert。insert只能跟在when not matched then的后面。
如图：
