Data Pump Parameter-PARTITION_OPTIONS


Data Pump Parameter-PARTITION_OPTIONS

The PARTITION_OPTIONS parameter
determines how partitions will be handled during export and import operations.
The syntax is shown below.


PARTITION_OPTIONS={none
|
departition |
merge}The allowable values are:

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.

The NONE and MERGE options
are not possible if the export was done using
the TRANSPORTABLE parameter with a partition or
subpartition
filter. If there are any grants on objects being
departitioned, an
error message is generated and the objects are not loaded.

Step:1

SQL> conn scott/tiger
Connected.


SQL> create table sale ( invoice number,sales INT NOT NULL)
PARTITION BY RANGE (sales)
( PARTITION sales_q1 VALUES LESS THAN (2014),
PARTITION sales_q2 VALUES LESS THAN (2015));  

Table created.

Step:2

SQL> select partition_name from user_tab_partitions where table_name=’SALE’;

PARTITION_NAME
——————————
SALES_Q1
SALES_Q2
Step:3
[oracle@oracle11g dump]$ expdp scott/tiger dumpfile=sales.dmp directory=test_dir tables=SALE
Export: Release 11.2.0.3.0 – Production on Mon Aug 31 14:24:33 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″:  scott/******** dumpfile=sales.dmp directory=test_dir tables=SALE
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SCOTT”.”SALE”:”SALES_Q1″                       0 KB       0 rows
. . exported “SCOTT”.”SALE”:”SALES_Q2″                       0 KB       0 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dump/sales.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 14:24:48
Step:4
SQL> Drop table sale  purge;
Table dropped.
Step:5
[oracle@oracle11g dump]$ impdp scott/tiger dumpfile=sales.dmp directory=test_dir partition_options=DEPARTITION
Import: Release 11.2.0.3.0 – Production on Mon Aug 31 14:26:11 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SCOTT”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_FULL_01″:  scott/******** dumpfile=sales.dmp directory=test_dir partition_options=DEPARTITION
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”SALE_SALES_Q1″                         0 KB       0 rows
. . imported “SCOTT”.”SALE_SALES_Q2″                         0 KB       0 rows
Job “SCOTT”.”SYS_IMPORT_FULL_01″ successfully completed at 14:26:17
Step:6
SQL> select * from tab where tname like ‘S%’;
TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
SALE_SALES_Q1                  TABLE
SALE_SALES_Q2                  TABLE
SALGRADE                       TABLE
SQL> select partition_name from user_tab_partitions where table_name=’SALE’;
no rows selected
Step:7
[oracle@oracle11g dump]$ impdp scott/tiger dumpfile=sales.dmp directory=test_dir partition_options=MERGE
Import: Release 11.2.0.3.0 – Production on Mon Aug 31 14:38:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SCOTT”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_FULL_01″:  scott/******** dumpfile=sales.dmp directory=test_dir partition_options=MERGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”SALE”:”SALES_Q1″                       0 KB       0 rows
. . imported “SCOTT”.”SALE”:”SALES_Q2″                       0 KB       0 rows
Job “SCOTT”.”SYS_IMPORT_FULL_01″ successfully completed at 14:38:39
Step:8
SQL> select * from tab where tname like ‘S%’;
TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
SALE                                       TABLE
SALE_SALES_Q1                  TABLE
SALE_SALES_Q2                  TABLE
SQL>  select partition_name from user_tab_partitions where table_name=’SALE’;
no rows selected
  • August 31, 2015 | 11 views