Convert partition table to single table in Oracle with IMPDP partition options

PARTITION_OPTIONS
It is used to specify with IMPDP process. Specifies how table partitions should be created during an import operation.
IMPDP will help to move all the partition data into a single non partition table with help of parameter PARTITION_OPTIONS.
PARTITION_OPTIONS has different option to choose from with export and import operation to manulplate the data.

PARTITION_OPTIONS={none | departition | merge}

NONE: EXPORT and IMPORT work exactly same as the structure present in source and destination(NO change – DEFAULT)
DEPARTITION: Each partition and sub-partition will create a new separate table and table named crated is a combination of the table and Partition/Sub-partition name.
MERGE: It will import all partition data into single table.

Example for converting partition to Single table

1. Export the partition table into dump file
expdp hr/manager dumpfile=employee.dmp directory=dbbackup tables=employee logfile=employeeexport.log

2. Merge all partition into single table when imported.
impdp hr/manager PARTITION_OPTIONS=MERGE DIRECTORY=dbbackup DUMPFILE=employee.dmp LOGFILE=employeeimport.log


Example for converting each partition into a table

1. Export the partition table into dump file
expdp hr/manager dumpfile=employee.dmp directory=dbbackup tables=employee logfile=employeeexport.log

2. With DEPARTITION option it will create table for each partition with naming TABLENAME_PARTITIONNAME.
impdp hr/manager dumpfile=employee.dmp directory=dbbackup partition_options=DEPARTITION LOGFILE=employeeimport.log