Data Pump: Importing partitioned objects into target DB w/o partitioning

From: Uwe Küchler <uwe_at_kuechler.org>
Date: Mon, 8 Jun 2009 11:17:10 +0200 (CEST)
Message-ID: <15230.193.24.34.77.1244452630.squirrel_at_mx1bln1.prossl.de>



Hello oracle-l'ers,

I have the following issue when trying to import partitioned tables from a source DB to a target DB without the partitioning option. In this scenario, the tables and indexes on the target are already present, solely without partitions. Release is 10.2.0.3.

  • First try: impdp user/pw directory=dump_dir dumpfile=thedump tables=my_part_table TABLE_EXISTS_ACTION=truncate This stopped with "ORA-00439: feature not enabled: Partitioning", although the table already existed! Seems as if the partitioning is checked before the table_exists_action is executed.
  • Second try: impdp user/pw directory=dump_dir dumpfile=thedump tables=my_part_table TABLE_EXISTS_ACTION=append Table was truncated manually beforehand. Result: Same as above.
  • Third try: impdp user/pw directory=dump_dir dumpfile=thedump tables=my_part_table CONTENT=DATA_ONLY Table was truncated manually beforehand. Result: Success. BUT: This requires manual action on those objects and further means that you should manually de-/re-activate constraints and indexes on the table, at least when there's lots of data to load.

So, option #3 is not that good of an option, as there's much more fiddling involved than it was earlier with "imp ... IGNORE=Y". Does anyone have a better idea how to handle this? I'd love to see a "use_partitions=N" option in impdp, but haven't found it so far. ;-)
Is there any possibility to import formerly partitioned objects into an existing, non-partitioned object?

Regards,
Uwe

P.S.: Yes I know, the Index/Constraint part of it was more or less the same in the "imp age". Nevertheless, there's still much more manual action needed with Data Pump than I am happy with.

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 08 2009 - 04:17:10 CDT

Original text of this message