Datapump IMPDP Partition_Options = MERGE , DEPARTITION Examples

advertisements

_____________________________________________________________________________________________________________________

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

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

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

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

. .

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(*)

----------

_____________________________________________________________________________________________________________________

Website Stats

Datapump IMPDP Partition_Options = MERGE , DEPARTITION Examples

0 comments:

Post a Comment

Archive

Labels