impdp请教下大家都是怎么追加导入分区表的。

zgb1187270032

请教个问题
A库有表table1 ，是个分区表，数据分区是2012,2013,2014,2015,。B库有表tables1，这个表是A库table1表在2014年迁移时生成的，也是一个分区表。数据分区是2012,2013.也就是说，这两个表结构完全一致，因为B库的表就是A库生成的，只不过少分区。
问题:我现在想把A库table1表2014分区的数据导入到B库table1里。
impdp 正常导入是不行，会提示表存在直接跳过，如果使用table_exists_action= replace 参数能导入成功，但是里面原来2012,2013分区的数据都清空了，就剩下2014你导入的数据了，这样也不行。使用table_exists_action=apple参数也不行会直接报错，如果不是分区表是可以这么做的，在尾部追加数据，但是分区表会报错。请教下大家都是怎么追加导入分区表的。

big_pig110

导入前先允许行迁移，alter table xxx enable row movment ;
再执行table_exists_action=append
注意：引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。

zgb1187270032

big_pig110 发表于 2015-10-16 10:57
导入前先允许行迁移，alter table xxx enable row movment ;
再执行table_exists_action=append
注意：引 ...

那索引什么的是不是全失效了

big_pig110

你导入后，索引会重建一下，但是最好检查一下是否存在无效对象

bfc99

在B库的TABLE1表上增加相应的分区，然后再带上table_exists_action=append 来导入。

zgb1187270032

bfc99 发表于 2015-10-16 15:00
在B库的TABLE1表上增加相应的分区，然后再带上table_exists_action=append 来导入。

…………………… 你没看我发的问题吗？我都说了分区表不能用table_exists_action=append 导入。导入会报错，建立出分区也不让导入

vage_fang

我经常用expdp/impdp迁移历史分区的，impdp的时候指定table_exists_action=append追加导入，没有出现过你所说的报错，你最好把报错信息，及impdp的语句，都贴出来，让大伙看的时候免得理解有误。。你说呢

zgb1187270032

vage_fang 发表于 2015-10-26 15:51
我经常用expdp/impdp迁移历史分区的，impdp的时候指定table_exists_action=append追加导入，没有出现过你所 ...

恩 你说的对，我现在数据已经迁移完事了，用的迁移分区。append当时报错意思是导入数据顺序的问题。现在找不到了，谢谢大家的回答。

zgb1187270032

bfc99 发表于 2015-10-16 15:00
在B库的TABLE1表上增加相应的分区，然后再带上table_exists_action=append 来导入。

哥们 谢谢你的回答，我这个库导入说导入顺序的报错，日志我现在找不到了，我用分区迁移搞定的，谢谢回答

bfc99

zgb1187270032 发表于 2015-10-26 15:08
…………………… 你没看我发的问题吗？我都说了分区表不能用table_exists_action=append 导入。导入会报 ...

看我的实验：select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE        11.2.0.3.0        Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

--创建表table1,模拟在A库上的table1表。
SQL> create table table1 (
id number,
dt date)
partition by range(dt)
(partition p2012 values less than (date'2013-01-01'),
partition p2013 values less than (date'2014-01-01'),
partition p2014 values less than (date'2015-01-01'),
partition p2015 values less than (date'2016-01-01')
);  2    3    4    5    6    7    8    9  

Table created.

--插入四条数据，以使各分区均有数据。
SQL> insert into table1 values(1,date'2012-1-5');

1 row created.

SQL> insert into table1 values(2,date'2013-2-3');

1 row created.

SQL> insert into table1 values(3,date'2014-3-5');

1 row created.

SQL> insert into table1 values(4,date'2015-5-7');

1 row created.

SQL> commit;

Commit complete.

--检查四个分区，应该各有一条数据。
SQL> select * from table1 partition(p2012);

        ID DT
---------- ---------
         1 05-JAN-12

SQL> c/2012/2013
  1* select * from table1 partition(p2013)
SQL> /

        ID DT
---------- ---------
         2 03-FEB-13

SQL> c/2013/2014
  1* select * from table1 partition(p2014)
SQL> /

        ID DT
---------- ---------
         3 05-MAR-14

SQL> c/2014/2015
  1* select * from table1 partition(p2015)
SQL> /

        ID DT
---------- ---------
         4 07-MAY-15

--创建表table2,模拟B库上的tables表。为避免混淆，测试用表起名为table2.
SQL> create table table2 (
id number,
dt date)
partition by range(dt)
(partition p2012 values less than (date'2013-01-01'),
partition p2013 values less than (date'2014-01-01')
);  2    3    4    5    6    7  

Table created.

--插入与table1表中P2012和P2013两分区中一样的两条数据，以模拟已存在的数据。
SQL> insert into table2 values(1,date'2012-1-5');

1 row created.

SQL> insert into table2 values(2,date'2013-2-3');

1 row created.

SQL> commit;

Commit complete.
--检查table2中的数据，2分区中各有一条数据。
SQL> select * from table2 partition(p2012);

        ID DT
---------- ---------
         1 05-JAN-12

SQL> select * from table2 partition(p2013);

        ID DT
---------- ---------
         2 03-FEB-13

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--导出table1表中p2014分区中的数据，为后面的导入试验做准备。
[oracle@host11 ~]$ expdp demo/demo directory=datapump dumpfile=exp_table1_2014.dmp tables=table1:p2014 logfile=exp_table1_2014.log;

Export: Release 11.2.0.3.0 - Production on Mon Oct 26 16:11:31 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DEMO"."SYS_EXPORT_TABLE_01":  demo/******** directory=datapump dumpfile=exp_table1_2014.dmp tables=table1:p2014 logfile=exp_table1_2014.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEMO"."TABLE1":"P2014"                     5.406 KB       1 rows
Master table "DEMO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_TABLE_01 is:
  /oradata2/dpdump/exp_table1_2014.dmp
Job "DEMO"."SYS_EXPORT_TABLE_01" successfully completed at 16:11:48

--如果不在table2表中添加相应的分区，导入时会报错。
[oracle@host11 ~]$ impdp demo/demo directory=datapump dumpfile=exp_table1_2014.dmp remap_tables=table1:table2 table_exists_action=append logfile=impdp_table1_201
4.log;
LRM-00101: unknown parameter name 'remap_tables'

[oracle@host11 ~]$ impdp demo/demo directory=datapump dumpfile=exp_table1_2014.dmp remap_table=table1:table2 table_exists_action=append logfile=impdp_table1_2014
.log

Import: Release 11.2.0.3.0 - Production on Mon Oct 26 16:14:08 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  demo/******** directory=datapump dumpfile=exp_table1_2014.dmp remap_table=table1:table2 table_exists_action=append logfile=impdp_table1_2014.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DEMO"."TABLE2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "DEMO"."TABLE2":"P2014" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-14400: inserted partition key does not map to any partition     
Job "DEMO"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 16:14:12

[oracle@host11 ~]$ sqlplus demo/demo

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 26 16:14:50 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--为table2表添加相应的分区
SQL> alter table table2 add partition p2014 values less than (date'2015-01-01');

Table altered.

--此时该分区中没有任何数据
SQL> select * from table2 partition(p2014);

no rows selected

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--重新导入
[oracle@host11 ~]$ impdp demo/demo directory=datapump dumpfile=exp_table1_2014.dmp remap_table=table1:table2 table_exists_action=append logfile=impdp_table1_2014
.log

Import: Release 11.2.0.3.0 - Production on Mon Oct 26 16:17:26 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  demo/******** directory=datapump dumpfile=exp_table1_2014.dmp remap_table=table1:table2 table_exists_action=append logfile=impdp_table1_2014.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DEMO"."TABLE2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."TABLE2":"P2014"                     5.406 KB       1 rows
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at 16:17:28

[oracle@host11 ~]$ sqlplus demo/demo

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 26 16:17:40 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--检查table2表中的数据
SQL> select * from table2;

        ID DT
---------- ---------
         1 05-JAN-12
         2 03-FEB-13
         3 05-MAR-14

SQL> select * from table2 partition(p2014);

        ID DT
---------- ---------
         3 05-MAR-14