DOYENSYS Knowledge Portal




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




Monday, August 31, 2015

Data Pump Parameter-REMAP_DATA



Data Pump Parameter-REMAP_DATA


EXPDP REMAP_DATA parameter is used to transform the column data to some other value using the database packages. Using this parameter you can translate or transform the critical informations like address, credit card number to some other values taking the export itself. This parameter can be used with import (IMPDP) utility also.

Using REMAP_DATA option data can be manipulated during the export and import job.

This feature can be used for protecting sensitive information during the export and import job.



SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80             800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81        2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81      2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87         3000                    20
      7839 KING       PRESIDENT            17-NOV-81             5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81      1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87         1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81             950                    30
      7902 FORD       ANALYST         7566 03-DEC-81         3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82           1300                    10

14 rows selected.

SQL> create or replace package sal_pkg as  function sal_fun (sal  number)  return number;  end;
/

Package created.

SQL> create or replace package body sal_pkg as function sal_fun(sal in number) return number
as
sals number;
begin
sals :=sal+10;
return(sals);
end;
end;
/

Package body created.


[oracle@oracle11g ~]$ expdp test/tiger directory=test_dir dumpfile=test%U.dmp tables=emp remap_data=test.emp.sal:test.sal_pkg.sal_fun

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 17:18:27 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 "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=test_dir dumpfile=test%U.dmp tables=emp remap_data=test.emp.sal:test.sal_pkg.sal_fun
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TEST"."EMP"                                8.578 KB      14 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /u01/dump/test01.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 17:18:41


SQL> delete from emp;

14 rows deleted.

SQL> commit;



[oracle@oracle11g ~]$ impdp test/tiger tables=emp directory=test_dir dumpfile=test%U.dmp content=data_only

Import: Release 11.2.0.3.0 - Production on Mon Aug 31 19:10:40 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 "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/******** tables=emp directory=test_dir dumpfile=test%U.dmp content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."EMP"                                8.578 KB      14 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 19:11:02



SQL> select sal from emp;

       SAL
----------
       810
      1610
      1260
      2985
      1260
      2860
      2460
      3010
      5010
      1510
      1110
       960
      3010
      1310



Data Pump Parameter-REMAP_SCHEMA


Data Pump Parameter-REMAP_SCHEMA


In conventional import (IMP) utility we havefromuser and touser parameter to import the data dump which is taken from one user to another user. In Data Pump impdp oracle introduced new parameter called REMAP_SCHEMA.

Syntax:
impdp REMAP_SCHEMA=source:target where source and target are the schema names.




[oracle@oracle11g ~]$expdp system/oracle schemas=scott directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log compression=all

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 17:00:49 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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              4.976 KB       4 rows
. . exported "SCOTT"."EMP"                               5.640 KB      14 rows
. . exported "SCOTT"."RAM"                               4.687 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          4.890 KB       5 rows
. . exported "SCOTT"."SITA"                              4.687 KB       1 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."SALE"                                  0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q1"                         0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q2"                         0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/remap_schema.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:01:09


SQL> set lines 1000 pages 1000
SQL> select username from dba_users;

USERNAME
------------------------------
DBSNMP
SCOTT
RAM
HR
SYSMAN
MGMT_VIEW
SYS
SYSTEM
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
WMSYS
ORACLE_OCM
APPQOSSYS
XS$NULL
APEX_030200
OWBSYS_AUDIT
BI
PM
MDDATA
IX
ORDDATA
CTXSYS
ANONYMOUS
SH
OUTLN
DIP
OE
APEX_PUBLIC_USER
XDB
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS

37 rows selected.


impdp system/oracle directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log remap_schema=scott:test


[oracle@oracle11g ~]$ impdp system/oracle directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log remap_schema=scott:test

Import: Release 11.2.0.3.0 - Production on Mon Aug 31 17:04:39 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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log remap_schema=scott:test
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT"                               4.976 KB       4 rows
. . imported "TEST"."EMP"                                5.640 KB      14 rows
. . imported "TEST"."RAM"                                4.687 KB       1 rows
. . imported "TEST"."SALGRADE"                           4.890 KB       5 rows
. . imported "TEST"."SITA"                               4.687 KB       1 rows
. . imported "TEST"."BONUS"                                  0 KB       0 rows
. . imported "TEST"."SALE"                                   0 KB       0 rows
. . imported "TEST"."SALE_SALES_Q1"                          0 KB       0 rows
. . imported "TEST"."SALE_SALES_Q2"                          0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:04:52




SQL> select username from dba_users;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
RAM
HR
TEST
OUTLN
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
IX
SH
DIP
OE
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR

38 rows selected.

Data Pump Parameter-REMAP_TABLE


Data Pump Parameter-REMAP_TABLE


This parameter allows a table to be renamed during the import operations performed using the TRANSPORTABLE method. It can also be used to alter the base table name used during PARTITION_OPTIONS imports. The syntax is shown below.

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

An example is shown below.


SQL> select * from ram;

        NO
----------
         1

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
RAM                            TABLE
SALE                           TABLE
SALE_SALES_Q1                  TABLE
SALE_SALES_Q2                  TABLE
SALGRADE                       TABLE

8 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ expdp scott/tiger dumpfile=ramtest.dmp logfile=ramlog.log  tables=ram directory=TEST_DIR

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 16:52:08 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=ramtest.dmp logfile=ramlog.log tables=ram directory=TEST_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."RAM"                               5.007 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dump/ramtest.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:52:18

[oracle@oracle11g ~]$
[oracle@oracle11g ~]$ impdp scott/tiger  dumpfile=ramtest.dmp logfile=ramlog.log remap_table=scott.ram:sita directory=TEST_DIR

Import: Release 11.2.0.3.0 - Production on Mon Aug 31 16:53:27 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=ramtest.dmp logfile=ramlog.log remap_table=scott.ram:sita directory=TEST_DIR
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."SITA"                              5.007 KB       1 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 16:53:31

[oracle@oracle11g ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 31 16:53:38 2015

Copyright (c) 1982, 2011, Oracle.  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

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
RAM                            TABLE
SALE                           TABLE
SALE_SALES_Q1                  TABLE
SALE_SALES_Q2                  TABLE
SALGRADE                       TABLE
SITA                           TABLE

9 rows selected.

SQL> select * from sita;

        NO
----------
         1


Existing tables are not renamed, only tables created by the import.

Data Pump Parameter-Filesize


Data Pump Parameter-Filesize


expdp system/oracle SCHEMAS=scott DUMPFILE =demo%U.dmp  FILESIZE=50k directory=TEST_DIR

[oracle@oracle11g dump]$ expdp system/oracle SCHEMAS=scott DUMPFILE =demo%U.dmp  FILESIZE=50k directory=TEST_DIR

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 15:01:28 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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** SCHEMAS=scott dumpfile=demo%U.dmp FILESIZE=50k directory=TEST_DIR
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."RAM"                                   0 KB       0 rows
. . exported "SCOTT"."SALE"                                  0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q1"                         0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q2"                         0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/demo01.dmp
  /u01/dump/demo02.dmp
  /u01/dump/demo03.dmp
  /u01/dump/demo04.dmp
  /u01/dump/demo05.dmp
  /u01/dump/demo06.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:01:59

[oracle@oracle11g dump]$ ls -lrt
total 264
-rw-r--r-- 1 oracle oinstall  2124 Aug 31 15:01 export.log
-rw-r----- 1 oracle oinstall 20480 Aug 31 15:01 demo06.dmp
-rw-r----- 1 oracle oinstall 49152 Aug 31 15:01 demo05.dmp
-rw-r----- 1 oracle oinstall 49152 Aug 31 15:01 demo04.dmp
-rw-r----- 1 oracle oinstall 49152 Aug 31 15:01 demo03.dmp
-rw-r----- 1 oracle oinstall 49152 Aug 31 15:01 demo02.dmp
-rw-r----- 1 oracle oinstall 49152 Aug 31 15:01 demo01.dmp

Data Pump Parameter-dumpfile=%U



Data Pump Parameter-dumpfile=%U

Filename can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99.


[oracle@oracle11g dump]$ expdp scott/tiger schemas=scott dumpfile=scott%u.dmp parallel=2 directory=TEST_DIR

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 14:49:39 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_SCHEMA_01":  scott/******** schemas=scott dumpfile=scott%u.dmp parallel=2 directory=TEST_DIR
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."RAM"                                   0 KB       0 rows
. . exported "SCOTT"."SALE"                                  0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q1"                         0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q2"                         0 KB       0 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/scott01.dmp
  /u01/dump/scott02.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:51:27

Data Pump Parameter-REUSE_DUMPFILES


Data Pump Parameter-REUSE_DUMPFILES



The REUSE_DUMPFILES parameter can be used to prevent errors being issued if the export attempts to write to a dump file that already exists.



REUSE_DUMPFILES={Y | N}When set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.


[oracle@oracle11g dump]$ expdp scott/tiger tables=emp directory=TEST_DIR dumpfile=TEST.dmp

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 14:46:16 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/******** tables=emp directory=TEST_DIR dumpfile=TEST.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dump/TEST.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:46:32

[oracle@oracle11g dump]$ expdp scott/tiger tables=emp directory=TEST_DIR dumpfile=TEST.dmp

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 14:46:40 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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/u01/dump/TEST.dmp"
ORA-27038: created file already exists
Additional information: 1


[oracle@oracle11g dump]$ expdp scott/tiger tables=emp directory=TEST_DIR dumpfile=TEST.dmp reuse_dumpfiles=y

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 14:46:55 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/******** tables=emp directory=TEST_DIR dumpfile=TEST.dmp reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dump/TEST.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:47:07



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