CREATE or REPLACE DIRECTORY datapump AS 'C:\temp\datapump';

In addition to Data Masking Pack in Grid Control, Oracle Data Pump provides a method to mask data:  REMAP_DATA parameter introduced in Oracle Database 11g.
Oracle Data Pump’s REMAP_DATA feature uses a remapping function to rewrite data.
For example, a column with phone numbers could be replaced by a numbers generated by a REMAP_DATA function.

REMAP_DATA allows transformation of column’s data while exporting (expdp) or importing (impdp) by using a remapping function in the database.
REMAP_DATA with Data Pump is usually faster than a custom UPDATE statement for masking data.
To mask multiple columns in the same process and command, the REMAP_DATA parameter can be used multiple times.

Restrictions
- Data types must be same in the table column, masking function parameter, and function return value.
- No commits or rollbacks should be done in the masking function.
- No direct path loads can be used in import process with REMAP_DATA.

Note: Operation of long export/import data pump processes can be monitored from the v$session_longops view, but the estimated values do not take into account REMAP_DATA operations.

Quick Example:
REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

[schema1.]tablename.column_name:
[schema2.]pkg.function

• schema1 -- the schema with the table to be remapped. By default, this is the schema of the user doing the export.
• tablename -- the table which column will be remapped.
• column_name -- which is to be remapped.
• schema2 -- with  the PL/SQL package for remapping function. As a default, this is the schema of the user doing the export.
• pkg -- the name of the PL/SQL package with the remapping function.
• function -- the name of the remap function in the PL/SQL package

Create a table in the CUSTOMERS schema called phones

CREATE TABLE CUSTOMERS.PHONES (
MODELNAME VARCHAR(20) NOT NULL,
PHONENUMBER VARCHAR2(50));
insert into CUSTOMERS.PHONES values(’N900’,’+3581234567’);
insert into CUSTOMERS.PHONES values(’N8’,’+3589817654’);
insert into CUSTOMERS.PHONES values(’N7’,’+3584834819’);
Commit;

We then need to create a function for remapping. The function masknumber will accept a varchar2 type and returns a random phone number in varchar2 type

create or replace package customers.maskpkg
as
     function masknumber(phonenumber varchar2) return varchar2;
end;
/
create or replace package body customers.maskpkg as
   function masknumber (phonenumber varchar2) return varchar2 is
   begin
      return substr(phonenumber,1,4)||round(dbms_random.value (100,999))|| lpad(round(dbms_random.value (1,9999)),4,'0');
   end;
end;
/

This example will mask one column: phonenumber to the export output file
expdp customers tables=customers.phones dumpfile=phones_masked.dmp directory=dumpdir remap_data=customers.phones.phonenumber:customers.maskpkg.masknumber

REMAP_DATA parameter can also be used in the import process
impdp dumpfile=data.dmp REMAP_DATA=scott.orders.customer_name:scott.maskpkg.mask
expdp dumpfile=data.dmp REMAP_DATA=scott.orders.customer_name:scott.maskpkg.mask

Compression
Usually, when you have to use expdp you need to compress/uncompress the exported file, adding more time and also taking more CPU time. In oracle 11g, there is no need to use OS-level compress utility, the COMPRESSION option not only is faster, it also makes the file much smaller. Valid values are: ALL, (METADATA_ONLY), DATA_ONLY and NONE.

Example:
nohup expdp system schemas=test directory=data_pump_dir dumpfile=test.dmp logfile=test.log compression=all &

In all this cases we will be using ORCL as the original DB, and DEST as the destination DB.

Scenario1 Export the whole ORCL database.
expdp userid=system/password@ORCL dumpfile=expfulldp.dmp logfile=expfulldp.log full=y directory=dumplocation

Scenario2 Export the scott schema from ORCL and import into DEST database.
expdp userid=system/password@ORCL dumpfile=schemaexpdb.dmp logfile=schemaexpdb.log directory=dumplocation schemas=scott
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation

Another Example: While import, exclude some objects(sequence,view,package,cluster,table). Load the objects which came from RES tablespace into USERS tablespace in target database.
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation table_exists_action=replace remap_tablespace=res:users exclude=sequence,view,package,cluster,table:"in('LOAD_EXT')"

Scenario 3 Clone a User
In the past when a DBA had the need to create a new user with the same structure (All objects, tablespaces quota, synonyms, grants, system privileges, etc) was a very painful experience, now all can be done very easily using Data Pump, let use as an example that you want to create the user ”Z” exactly like the user “A”, to achieve this goal all you will need to do is first export the schema “A” definition and then import it again saying to the Data Pump to change the schema “A” for the new schema named “Z” using the “remap_schema” parameter available with impdp.
expdp user/password schemas=A directory=datapump dumpfile=Schema_A.dmp                 [optional: content=metadata_only]
impdp user/password remap_schema=A:Z directory=datapump dumpfile= Schema_A.dmp
And your new user Z is now created like your existing user A , that easy!

Scenario 4 Create a Metadata File
You can generate a SQL File from an existing exported file. As an Example, I am going to expdp the Schema Fraudguard, after that, I will use the impdp command with the sqlfile option to generate a sql containing all the objects that I already exported:
expdp system schemas=fraudguard content=metadata_only directory=EXPORTPATH dumpfile=metadata_24112010.dmp
impdp system directory=EXPORTPATH dumpfile= metadata_24112010.dmp sqlfile=metadata_24112010.sql

Scenario5 Export the emp table from scott schema at ORCL instance and import into DEST instance.
expdp userid=system/password@ORCL logfile=tableexpdb.log directory=dumplocation tables=scott.part_emp dumpfile=tableexpdb.dmp
impdp userid=system/password@DEST dumpfile=tableexpdb.dmp logfile=tabimpdb.log directory=dumplocation table_exists_action=REPLACE

Scenario 6 Create smaller Copies of PROD
That is a very common task for a DBA, you have a task to create a copy of your Database (for development or test purpose) but your destination server don’t have enough space to create a full copy of it!
This can be easily solved with Data Pump, for this example, let say that you only have space for 70% of your production database, now to know how to proceed, we need to decide if the copy will contain metadata only (no data/rows) or if it will include the data also. Let’s see how to do each way:

a) Metadata Only
First do a full export of your source database.
expdp user/password content=metadata_only full=y directory=datapump dumpfile=metadata_24112010.dmp

Then, let’s import the metadata and tell the Data Pump to reduce the size of extents to 70%, you can do it using the parameter “transform” available with “impdp”, it represent the percentage multiplier that will be used to alter extent allocations and datafiles size.
impdp user/password transform=pctspace:70 directory=datapump dumpfile=metadata_24112010.dmp

b) Metadata and data
First does a full export of your source database using the export parameter “sample”, this parameter specify a percentage of the data rows to be sampled and unload from your source database, in this case let’s use 70%.
expdp user/password sample=70 full=y directory=datapump dumpfile=expdp_70_24112010.dmp

Then, all you need to do as the example before is to import it telling the Data Pump to reduce the size of extents to 70%, and that’s it!
impdp user/password transform=pctspace:70 directory=datapump dumpfile=expdp_70_24112010.dmp

Scenario 7 Export only specific partition in emp table from scott schema at orcl and import into ordb database.
expdp userid=system/password@ORCL dumpfile=partexpdb.dmp logfile=partexpdb.log directory=dumplocation tables=scott.part_emp:part10,scott.part_emp:part20

If we want to overwrite the exported data in target database, then we need to delete emp table for deptno in(10,20).
scott@DEST> delete part_emp where deptno in (10,20);
scott@DEST> commit;
impdp userid=system/password@DEST dumpfile=partexpdb.dmp logfile=tabimpdb.log directory=dumplocation table_exists_action=append

Scenario 8 Export only tables (no code) in scott schema at ORCL and import into DEST database
expdp userid=system/password@ORCL dumpfile=schemaexpdb.dmp logfile=schemaexpdb.log directory=dumplocation include=table schemas=scott
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation table_exists_action=replace

Scenario 9 Export only rows belonging to department 10 and 20 in emp and dept table from ORCLdatabase. Import the dump file in @DESTdatabase. While importing, load only deptno 10 in target database.
expdp userid=system/password@ORCL dumpfile=data_filter_expdb.dmp logfile=data_filter_expdb.log directory=dumplocation content=data_only schemas=scott include=table:"in('EMP','DEPT')" query="where deptno in(10,20)"

impdp userid=system/password@DEST dumpfile=data_filter_expdb.dmp logfile=data_filter_impdb.log directory=dumplocation schemas=scott query="where deptno = 10" table_exists_action=APPEND

Scenario 10 Export the scott schema from ORCLdatabase and split the dump file into 50M sizes. Import the dump file into DEST datbase.
Expdp parfile content:
userid=system/password@ORCL
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
filesize=50M
schemas=scott
include=table

As per the above expdp parfile, initially, schemaexp_split_01.dmp file will be created. Once the file is 50MB, the next file called schemaexp_split_02.dmp will be created. Let us say, the dump file size is 500MB, then it creates 10 dump file as each file size is 50MB.
Impdp parfile content:
userid=system/password@DEST
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant

Scenario 11 Export the scott schema from ORCL database and split the dump file into four files. Import the dump file into DEST datbase.
Expdp parfile content:
userid=system/password@ORCL
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
parallel=4
schemas=scott
include=table

As per the above parfile content, initially four files will be created - schemaexp_split_01.dmp, schemaexp_split_02.dmp, schemaexp_split_03.dmp, schemaexp_split_04.dmp. Notice that every occurrence of the substation variable is incremented each time. Since there is no FILESIZE parameter, no more files will be created.
Impdp parfile content:
userid=system/password@DEST
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant

Scenario 12 Export the scott schema from ORCL database and split the dump file into three files.
The dump files will be stored in three different location. This method is especially useful if you do not have enough space in one file system to perform the complete expdp job. After export is successful, import the dump file into DEST database.
Expdp parfile content:
userid=system/password@ORCL
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
filesize=50M
schemas=scott
include=table

As per above expdp par file content, it place the dump file into three different location. Let us say, entire expdp dump file size is 1500MB. Then it creates 30 dump files(each dump file size is 50MB) and place 10 files in each file system.
Impdp parfile content:
userid=system/password@DEST
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
table_exists_action=replace


Scenario 13 Expdp scott schema in ORCL and impdp the dump file in training schema in DEST database.
expdp userid=scott/tiger@ORCL logfile=netwrokexp1.log directory=dumplocation dumpfile=networkexp1.dmp schemas=scott include=table

impdp userid=system/password@DEST logfile=networkimp1.log directory=dumplocation dumpfile=networkexp1.dmp table_exists_action=replace remap_schema=scott:training

Scenario 14 Expdp table on ORCL database and imdp in DEST. When we export the data, export only 20 percent of the table data. We use SAMPLE parameter to accomplish this task.
SAMPLE parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. The sample_percent indicates the probability that a block of rows will be selected as part of the sample. It does not mean that the database will retrieve exactly that amount of rows from the table. The value you supply for sample_percent can be anywhere from .000001 up to, but not including, 100.
If no table is specified, then the sample_percent value applies to the entire export job. The SAMPLE parameter is not valid for network exports.
expdp userid=system/password@ORCL dumpfile=schemaexpdb.dmp logfile=schemaexpdb.log directory=dumplocation tables=scott.part_emp SAMPLE=20

As per the above expdp parfile, it exports only 20 percent of the data in part_emp table.
impdp userid=system/password@DEST dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation table_exists_action=replace

Exclude Objects
The exclude parameter allows any database object type to be excluded from an export or import operation. The optional name qualifier allows you finer selectivity within each object type specified. For example, the following three lines in a parameter file:
Exclude=function
Exclude=procedure
Exclude=package:”like ‘PAYROLL%' “
Would exclude all functions, procedures and packages with names starting with ‘PAYROLL' from the job.

Include
The include parameter includes only the specified object types and objects in an operation. For example, if the above three specifications were INCLUDE parameters in a full database export, only functions, procedures and packages with names starting with ‘PAYROLL' would be written to the dumpfile set. You can use the clause INCLUDE=TABLE:"LIKE 'TAB%'" to export only those tables whose name start with TAB. Similarly, you could use the construct INCLUDE=TABLE:"NOT LIKE 'TAB%'" to exclude all tables starting with TAB. Alternatively you can use the EXCLUDE parameter to exclude specific objects.

Content
The content parameter allows one to request for the current operation just metadata, just data or both. The exp ‘ROWS=N' parameter is equivalent to content=metadata_only, but there is no equivalent for content=data_only.

Query
The query parameter operates much as it did in original export, but with two significant enhancements:
1.    It can be qualified with a table name such that it only applies to that table
2.    It can be used during import as well as export.

table_exists_action
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables. Other uses:
    * skip
    * append
    * truncate
    * replace

I/O BANDWIDTH IS MOST IMPORTANT FACTOR
It is important to make sure there is sufficient I/O bandwidth to handle the number of parallel threads specified; otherwise performance can actually degrade with additional parallel threads. Care should be taken to make sure the dumpfile set is located on spindles other than those holding the instance's data files. Wildcard file support makes it easy to spread the I/O load over multiple spindles. For example, a specification such as:
Dumpfile=dmpdir1:full1%u.dmp,dmpdir2:full2%u.dmp
Dumpfile=dmpdir3:full3%u.dmp,dmpdir4:full4%u.dmp
will create files named full101.dmp,  full201.dmp, full301.dmp, full401.dmp, full102.dmp, full202.dmp, full302.dmp, etc. in a round-robin fashion across the four directories pointed to by the four directory objects.
In parallel mode, the status screen will show four worker processes. (In default mode, only one process will be visible.) All worker processes extract data simultaneously and show their progress on the status screen

INITIALIZATION PARAMETERS
Essentially no tuning is required to achieve maximum Data Pump performance. Initialization parameters should be sufficient out of the box.

• Make sure disk_asynch_io remains TRUE: It has no effect on those platforms whose file systems already support asynchronous I/O, but those that don't are significantly impacted by a value of FALSE.
• db_block_checksum's default value is FALSE, but if an integrity issue is being investigated requiring this to be set to TRUE, its impact on data loading and unloading is minimal; less than 5%.

Monitoring Export:
While Data Pump Export is running, press Control-C; it will stop the display of the messages on the screen, but not the export process itself. Instead, it will display the Data Pump Export prompt as shown below. The process is now said to be in "interactive" mode:

Export>
This approach allows several commands to be entered on that Data Pump Export job. To find a summary, use the STATUS command at the prompt:

Export> status
Job: CASES_EXPORT
  Operation: EXPORT                        
  Mode: TABLE                         
  State: EXECUTING                     
  Degree: 1
  Job Error Count: 0
  Dump file:  /u02/dpdata1/expCASES.dmp
      bytes written =  2048

Worker 1 Status:
  State: EXECUTING                     
  Object Schema: DWOWNER
  Object Name: CASES
  Object Type: TABLE_EXPORT/TBL_TABLE_DATA/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 4687818

Remember, this is merely the status display. The export is working in the background. To continue to see the messages on the screen, use the command CONTINUE_CLIENT from the Export prompt.
While Data Pump jobs are running, you can pause them by issuing STOP_JOB on the Data Pump Export or Data Pump Import prompts and then restart them with START_JOB.
This functionality comes in handy when you run out of space and want to make corrections before continuing.

A simple way to gain insight into the status of a Data Pump job is to look into a few views maintained within the Oracle instance the Data Pump job is running.
These views are DBA_DATAPUMP_JOBS, DBA_DATAPUMP_SESSIONS, and V$SESSION_LOGOPS and they are critical in the monitoring of your export jobs so, you can attach to a Data Pump job and modify the execution of the that job.

DBA_DATAPUMP_JOBS
This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

select * from dba_datapump_jobs
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------
JKOOP      SYS_EXPORT_FULL_01     EXPORT     FULL       EXECUTING     1          1
JKOOP      SYS_EXPORT_SCHEMA_01   EXPORT     SCHEMA     EXECUTING     1          1

DBA_DATAPUMP_SESSIONS
This view give gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information.

SELECT * FROM DBA_DATAPUMP_SESSIONS
OWNER_NAME JOB_NAME                       SADDR
---------- ------------------------------ --------
JKOOPMANN  SYS_EXPORT_FULL_01             225BDEDC
JKOOPMANN  SYS_EXPORT_SCHEMA_01           225B2B7C

V$SESSION_LONGOPS
This view helps determine how well a Data Pump export is doing. It also shows you any operation that is taking long time to execute.
Basically gives you a progress indicator through the MESSAGE column.

select username, opname, target_desc,
       sofar, totalwork, message 
from V$SESSION_LONGOPS

USERNAME OPNAME               TARGET_DES SOFAR TOTALWORK  MESSAGE
-------- -------------------- ---------- ----- ---------- ------------------------------------------------
JKOOP    SYS_EXPORT_FULL_01   EXPORT       132        132 SYS_EXPORT_FULL_01:EXPORT:132 out of 132 MB done
JKOOP    SYS_EXPORT_FULL_01   EXPORT        90        132 SYS_EXPORT_FULL_01:EXPORT:90 out of 132 MB done
JKOOP    SYS_EXPORT_SCHEMA_01 EXPORT        17         17 SYS_EXPORT_SCHEMA_01:EXPORT:17 out of 17 MB done
JKOOP    SYS_EXPORT_SCHEMA_01 EXPORT        19         19 SYS_EXPORT_SCHEMA_01:EXPORT:19 out of 19 MB done

set serveroutput on size 999999
exec print_table('select * from v$session_longops where sid = 9');

SID                           : 9
SERIAL#                       : 68
OPNAME                        : Transaction Rollback
TARGET                        :
TARGET_DESC                   : xid:0x000e.01c.00000067
SOFAR                         : 10234
TOTALWORK                     : 20554
UNITS                         : Blocks
START_TIME                    : 07-dec-2003 21:20:07
LAST_UPDATE_TIME              : 07-dec-2003 21:21:24
TIME_REMAINING                : 77
ELAPSED_SECONDS               : 77
CONTEXT                       : 0
MESSAGE                       : Transaction Rollback: xid:0x000e.01c.00000067 :
                                 10234 out of 20554 Blocks done
USERNAME                      : SYS
SQL_ADDRESS                   : 00000003B719ED08
SQL_HASH_VALUE                : 1430203031
SQL_ID                        : 306w9c5amyanr
QCSID                         : 0