Datapump IMPDP Partition_Options = MERGE , DEPARTITION Examples


Partition_Options parameter is available with data pump impdp utility.  This parameter determines how the partitions available in the export dump are handled in the import operation.


Available options are PARTITION_OPTIONS={none | departition | merge}

None: Default parameter value is none. This parameter imports the partitioned tables as it is present in the source database.

Departition: If you specify the parameter value as departition, all the partitions and sub partitions will get imported into individual tables.  So after import you can see separate tables with names of the combination of table and sub-partition or table and sub-partition.

Merge: This option will import all partition data into single table.

Note: If the export dump is taken with transportable option then you cannot use the merge option. Also, index, grants will get failed if you use departition option as the main table will not present after the import.

Examples
I have table a table called PART_TEST with more than 300 paritions/subpartions

SQL> SELECT TABLE_NAME, COUNT(*) FROM dba_tab_partitions where table_name='PART_TEST' GROUP BY TABLE_NAME;

TABLE_NAME                       COUNT(*)
------------------------------ ----------
PART_TEST                           68

Example for Merge option

I have taken the export for the table PART_TEST and I am going to drop the table
SQL> DROP TABLE SCOTT.PART_TEST;

Table dropped.

SQL> PURGE RECYCLEBIN;

Recyclebin purged.

Import the table with merge option
$ impdp job_name=schemaexp full=y dumpfile=exp_xml_doc.dmp logfile=impexp_xml_doc.log directory=exp_dir remap_schema=orabpel:SCOTT remap_tablespace=orabpel:users partition_options=merge


Import: Release 11.2.0.2.0 - Production on Fri Oct 26 06:44:54 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SCHEMAEXP" successfully loaded/unloaded
Starting "SYS"."SCHEMAEXP":  /******** AS SYSDBA job_name=schemaexp full=y dumpfile=exp_xml_doc.dmp logfile=impexp_xml_doc.log directory=exp_dir remap_schema=orabpel:SCOTT remap_tablespace=orabpel:users partition_options=merge
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."PART_TEST":"SYS_P8410"."SYS_SUBP8407"  11.36 MB     225 rows
. . imported "SCOTT"."PART_TEST":"SYS_P8410"."SYS_SUBP8406"  9.926 MB     202 rows
.......
.......
. . imported "SCOTT"."PART_TEST":"SYS_P9043"."SYS_SUBP9040"      0 KB       0 rows
. . imported "SCOTT"."PART_TEST":"SYS_P9043"."SYS_SUBP9042"      0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SCHEMAEXP" successfully completed at 06:47:02

After import all partitions are removed and it is imported single table.

SQL> SELECT TABLE_NAME, COUNT(*) FROM dba_tab_partitions where table_name='PART_TEST' GROUP BY TABLE_NAME;

no rows selected

SQL> SQL> select table_name , partitioned from dba_tables where table_name='PART_TEST';

TABLE_NAME                     PAR
------------------------------ ---
PART_TEST                      NO

SQL> select count(*) from PART_TEST;

  COUNT(*)
----------
     10446

Example for DEPARTITION option
SQL> drop table SCOTT.PART_TEST;

Table dropped.

SQL>  PURGE RECYCLEBIN;

Recyclebin purged.

 impdp job_name=schemaexp full=y dumpfile=exp_xml_doc.dmp logfile=impexp_xml_doc.log directory=exp_dir remap_schema=orabpel:SCOTT remap_tablespace=orabpel:users partition_options=DEPARTITION

Import: Release 11.2.0.2.0 - Production on Fri Oct 26 06:53:00 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SCHEMAEXP" successfully loaded/unloaded
Starting "SYS"."SCHEMAEXP":  /******** AS SYSDBA job_name=schemaexp full=y dumpfile=exp_xml_doc.dmp logfile=impexp_xml_doc.log directory=exp_dir remap_schema=orabpel:SCOTT remap_tablespace=orabpel:users partition_options=DEPARTITION
Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39151: Table "SCOTT"."PART_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."PART_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."PART_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."PART_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
..
. .
. .
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."PART_TEST_SYS_SUBP8407"       11.36 MB     225 rows
. . imported "SCOTT"."PART_TEST_SYS_SUBP8406"       9.926 MB     202 rows
. .
. .
. . imported "SCOTT"."PART_TEST_SYS_SUBP9042"           0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE UNIQUE INDEX "SCOTT"."XML_DOC_PK" ON "SCOTT"."PART_TEST" ("DOCKEY") REVERSE PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SCOTT"."XML_DOC_PK" creation failed
Job "SYS"."SCHEMAEXP" completed with 147 error(s) at 07:36:11

After import all partitions and sub partitions imported into individual tables and the following query shows 399 tables. Index creation will get fail during the departition option.

SQL>  select count(*) from tab where tname like 'PART_TEST%';

  COUNT(*)
----------
       399

SQL> select count(*) from tab where tname ='PART_TEST';

  COUNT(*)
----------
         0
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google