Datapump PARTITION_OPTION parameter

PARTITION_OPTIONS=  NONE | DEPARTITION | MERGE

·        NONE:  The partitions are created exactly as they were on the system the export was taken from.

·        DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-) partition name.

·        MERGE: Combines all partitions into a single table.

Here I want to Import schema1 partitioned tables to schema2 non-partitioned tables, to do so

    1) Take data pump complete Export of schema1

    2) Import into schema2 as below

NOTE :    Here “schema2” is not there but creates automatically during the Datapump import process

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

$ impdp dumpfile=xxx.dmp directory=EXP remap_schema=schema1:schema2 PARTITION_OPTIONS=merge

Import: Release 11.2.0.2.0 - Production on Thu Sep 27 20:38:11 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_05" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_05":  /******** AS SYSDBA dumpfile=xxx.dmp directory=EXP remap_schema= schema1:schema2 PARTITION_OPTIONS=merge

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCHEMA2"."TABLE1":"SYS_P23561"  14.92 KB      34 rows

--------      OUTPUT TRIMMED          ------------------

Job "SYS"."SYS_IMPORT_FULL_05" completed with 12 error(s) at 20:39:53

A new schema2 will be created and all the partitioned tables are created as Single tables (non-partitioned)