DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, August 31, 2015

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




1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.