Data Pump

Data Pump replaces EXP and IMP. It provides high speed, parallel, bulk data and metadata movement of Oracle database contents across platforms and database versions. Oracle states that Data Pump's performance on data retrieval is 60% faster than Export and 20% to 30% faster on data input than Import. If a data pump job is started and fails for any reason before it has finished, it can be restarted at a later time. The commands to start the data pump are expdb and impdb, respectively. The data pump uses files as well as direct network transfer. Clients can detach and reconnect from/to the data pump. It can be monitored through several views like dba_datapump_jobs. The Data Pump's public API is the DBMS_DATAPUMP package.

To use Data Pump you must have EXP_FULL_DATABASE or IMP_FULL_DATABASE depending the operation to perform. These allow you to expdp & impdp across ownership for items such as grants, resource plans, schema definitions, and re-map, re-name, or re-distribute database objects or structures. By definition, Oracle gives permission to the objects in a DIRECTORY that a user would not normally have access to.
Data Pump runs only on the server side. You may initiate the export from a client but the job(s) and the files will run inside an Oracle server. There will be no dump files (expdat.dmp) or log files created on your local machine. Oracle creates dump and log files through DIRECTORY objects. So before you can use Data Pump you must create a DIRECTORY object. Example:

CREATE or REPLACE DIRECTORY datapump AS 'C:\user\datafile\datapump';

Then, as you use Data Pump you can reference this DIRECTORY as a parameter for export where you would like the dump or log files to end up.

The default name / location of Data Pump is DATA_PUMP_DIR at 'C:\oracle\product\10.2.0\admin\$ORACLE_SID\dpdump\' or /u01/app/oracle/admin/$ORACLE_SID/dpdump/

Advantages of Data Pump
1. We can perform export in parallel. It can also write to multiple files on different disks. (Specify parameters PARALLEL=2 and the two directory names with file specification DUMPFILE=ddir1:/file1.dmp, DDIR2:/file2.dmp)
2. Has the ability to attach and detach from job, monitor the job progress remotely.
3. Has more options to filter metadata objects. Ex, EXCLUDE, INCLUDE
4. ESTIMATE_ONLY option can be used to estimate disk space requirements before it performs the job
5. Data can be exported from remote database by using Database link
6. Explicit DB version can be specified, so only supported object types are exported.
7. During impdp, we can change the target file names, schema, and tablespace. Ex, REMAP_SCHEMA, REMAP_DATAFILES, REMAP_TABLESPACE
8. Has the option to filter data rows during impdp. Traditional exp/imp, we have this filter option only in exp. But here we have filter option on both impdp, expdp.
9. Data can be imported from one DB to another without writing to dump file, using NETWORK_LINK parameter.
10. Data access methods are decided automatically. In traditional exp/imp, we specify the value for the parameter DIRECT. But here, it decides where direct path can not be used , conventional path is used.
11. Job status can be queried directly from data dictionary(For example, dba_datapump_jobs, dba_datapump_sessions etc)



    Oracle 12c datapump have the following clauses:

     

    EXCLUDE – This is used to filter the metadata from the export operation. This is more like a where clause which you can use to exclude any object or schema from the export job.

    Example if you want a full database export except 1 schema (say TEST), then:-

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp FULL=YES EXCLUDE=SCHEMA:"='TEST'" LOGFILE=test.log

    Similarly suppose you want to exclude all indexes from a schema export and the condition is to export only indexes that starts with TEST_%, then:-

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST EXCLUDE=INDEX:"LIKE 'TEST_%'" LOGFILE=test.log

    So using this clause you can exclude any specific content from the dump, try and explore this option more. To get a list of available objects that can be excluded query the OBJECT_PATH column of DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS views.

     

    INCLUDE – Exactly opposite to EXCLUDE clause, this is used when you explicitly want to export, say only some tables, indexes along with views, packages in a schema dump, so only the the object types explicitly mentioned in the include clause would be imported and nothing else.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST INCLUDE=TABLE:"IN ('EMP', 'DEPT')" INCLUDE=PROCEDURE INCLUDE=INDEX:"LIKE 'TEST%'" LOGFILE=test.log

    So the above expdp command would only export tables EMP & DEPT, PROCEDURE and all indexes starting with TEST% no other objects of TEST schema would be exported.

     

    QUERY – This clause allows you to filter the data that gets exported, think of it as EXCLUDE & INCLUDE clause but with more control over filtering, thus this is more like a WHERE clause of the expdp command.

    When the QUERY parameter is specified for a table, Data Pump uses external tables to unload the target table.

    It accepts values in the form:- QUERY = [schema.][table_name:] query_clause

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST QUERY=TEST.EMP:"WHERE DEPT_ID > 90 AND SAL > 90000" LOGFILE=test.log

    Above command would export all tables of TEST schema but only specific records of EMP table.

     

    CLUSTER – This is used in RAC env. and used to spread the worker process on other oracle instances to utilize RAC resources for datapump jobs.

    It accepts values as:- CLUSTER=[YES | NO]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test_%U.dmp SCHEMAS=TEST CLUSTER=YES NOLOGFILE=YES

     

    SERVICE_NAME – This parameter is used with CLUSTER clause in RAC env. As you know CLUSTER clause if specified as YES allows MCP to use creates worker process in all the instances depending upon the degree of parallelism. But if you want to restrict the cluster resources for data pump you can specify SERVICE_NAME clause with CLUSTER=YES then MCP could only fork worker process on the instances that lies with the specified service name.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test_%U.dmp SCHEMAS=TEST CLUSTER=YES SERVICE_NAME=SERVICE_HA NOLOGFILE=YES

     

    ESTIMATE – This is used to estimate how much space each table in the export job will consume (in bytes).

    It accepts values as:- ESTIMATE=[BLOCKS | STATISTICS]

    • BLOCKS: This is calculated by multiplying the number of database blocks used by the source objects, and the block size.
    • STATISTICS: This is calculated using statistics for each table.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST ESTIMATE=STATISTICS LOGFILE=test.log

     

    ESTIMATE_ONLY – This is used to estimate the space that a job would consume, without actually performing the export operation.

    It accepts values as:- ESTIMATE_ONLY=[YES | NO]

    1

    expdp user/password SCHEMAS=TEST ESTIMATE_ONLY=YES

     

    FILESIZE – This is used to specify the maximum size of each dump file. When the max file size for the dump file is reached MCP then closes this file and creates a new file for further write operations.

    It accepts values as:- FILESIZE=integer[B | KB | MB | GB | TB]

    If using this clause please do remember to assign a variable in DUMPFILE parameter else you’ll receive ORA-39095: Dump file space has been exhausted: Unable to allocate xxxxxxx bytes.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test_%U.dmp SCHEMAS=TEST EXCLUDE=INDEX:"LIKE 'TEST_%'" FILESIZE=1GB LOGFILE=test.log

     

    FLASHBACK_SCN – If you want the dump to be consistent with a specific SCN this clause can be used. By default datapump dumps are consistent on a per table basis but if you want all the tables to be consistent then FLASHBACK_SCN or FLASHBACK_TIME clauses should be used, depending upon the requirement.

    Therefore the export operation is performed with data that is consistent up to the specified SCN in the FLASHBACK_SCN clause.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST COMPRESSION=DATA_ONLY FLASHBACK_SCN=103713102 LOGFILE=test.log

    To get the current SCN value you can use the below queries:-

    1

    2

    3

    SELECT current_scn FROM v$database;

    SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

    SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;

    To convert time to SCN you can use TIMESTAMP_TO_SCN function as:-

    1

    SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;

     

    FLASHBACK_TIME – This is similar to FLASHBACK_SCN, in this case the SCN that most closely matches the specified time is found, and then this SCN is used to get the consistent data.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST COMPRESSION=DATA_ONLY FLASHBACK_TIME="TO_TIMESTAMP('21-09-2016 12:36:00', 'DD-MM-YYYY HH24:MI:SS')" NOLOGFILE=YES REUSE_DUMPFILES=YES

     

    FULL – This is used when you want to export full data dump.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_%U.dmp full=Y PARALLEL=6 ENCRYPTION=data_only ENCRYPTION_PASSWORD=T3sT NOLOGFILE=YES REUSE_DUMPFILES=YES

    JOB_NAME – This is used to give a specific name to the export job, by default the job name is in the format SYS_EXPORT__NN.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_%U.dmp full=Y PARALLEL=6 ENCRYPTION=data_only ENCRYPTION_PASSWORD=T3sT NOLOGFILE=YES JOB_NAME=Encrypt_full_dump REUSE_DUMPFILES=YES

     

    KEEP_MASTER – As we saw in Oracle datapump Architecture & Internals master table is used to store information (Export/import parameters, current status, object info etc) about the data pump job and is automatically deleted once the job is completed successfully or killed.

    But what if you want to retain the information stored in master table to analyze the job, KEEP_MASTER clause is used if we need to retain the master table even after a successful job or killed job.

    It accepts values as:- KEEP_MASTER=[YES | NO]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_%U.dmp full=Y PARALLEL=6 ENCRYPTION=data_only ENCRYPTION_PASSWORD=T3sT NOLOGFILE=YES JOB_NAME=Encrypt_full_dump KEEP_MASTER=YES

     

    LOGTIME – This clause is used to include timestamp in the output messages.

    It accepts values as:- LOGTIME=[NONE | STATUS | LOGFILE | ALL]

    • NONE : No timestamps are included in the output.
    • STATUS : Timestamps are included in the standard output, but not in the log file.
    • LOGFILE : Timestamps are included in output to the log file, but not in the standard output.
    • ALL : Timestamps are included both to the standard output as well as to the log file.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST COMPRESSION=DATA_ONLY FLASHBACK_TIME="TO_TIMESTAMP('21-09-2016 12:36:00', 'DD-MM-YYYY HH24:MI:SS')" LOGTIME=ALL REUSE_DUMPFILES=YES

     

    METRICS – This clause is used to include additional information about the export job in the log file.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST CLUSTER=NO METRICS=YES

     

    NETWORK_LINK – Used in cases where database is exported via a database Link over the network. The data from the source database instance is written to a dump file set on the connected database instance.

    This means that the system to which the expdp client is connected contacts the source database referenced by the DB link and writes the data to a dump file set back on the connected system.

    1

    expdp user/password FULL=YES DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp EXCLUDE=SCHEMA:"='TEST'" NETWORK_LINK=TEST_LINK LOGFILE=test.log

     

    NOLOGFILE – This clause can be used to suppress the creation of logfile.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_%U.dmp full=Y PARALLEL=6 ENCRYPTION=data_only ENCRYPTION_PASSWORD=T3sT NOLOGFILE=YES JOB_NAME=Encrypt_full_dump REUSE_DUMPFILES=YES

     

    PARALLEL – This clause is used to authorize MCP to fork worker process and then further parallel query process upto the integer specified with the PARALLEL clause.

    Since each active worker process and slave process writes exclusively on the dumpfiles to utilize actual parallelization benefits the DUMPFILE needs to be specified with a variable so that MCP could open appropriate number of files to accommodate the integer specified with PARALLEL clause.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_%U.dmp full=Y PARALLEL=6 NOLOGFILE=YES JOB_NAME=Encrypt_full_dump LOGTIME=ALL

     

    PARFILE – There can be times when there are several clauses of expdp used or due to operating system special characters the expdp parameters are impacted, in such cases you can use a parameter file specifying all the parameters as per your requirement and specify the parameter file with PARFILE Clause.

    Here is a parameter file (say, full.par):-

    1

    2

    3

    4

    5

    6

    7

    DIRECTORY=DATA_PUMP_DIR

    DUMPFILE=full_%U.dmp

    full=Y

    PARALLEL=6

    NOLOGFILE=YES

    JOB_NAME=Encrypt_full_dump

    LOGTIME=ALL

    And it can be called via PARFILE clause, as:-

    1

    expdp user/password PARFILE=full.par

     

    REMAP_DATA – This clause is mostly used to mask the data when moving production dump to a test system. So using remap_data clause sensitive data can be replaced with random data.

    It accepts values in the form:- REMAP_DATA=[schema.]table_name.column_name:[schema.]pkg.function

    • schema — schema containing the table to be remapped. By default, this is the schema of the user doing the export.
    • table_name — table whose column will be remapped.
    • column_name — column whose data is to be remapped.
    • schema — schema containing the PL/SQL package that contains the remapping function.
    • pkg — name of the PL/SQL package containing the remapping function.
    • function — name of the function within the PL/SQL that will be called to remap the column table in each row of the specified table.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST REMAP_DATA=TEST.EMP.DEPT_ID:EMP.REMAP.MIN10  METRICS=YES LOGFILE=test.log

     

    REUSE_DUMPFILES – If the dumpfile with the same name as you specified in the DUMPFILE clause is found at the path specified under the directory path, datapump would abort giving error.

    You can however instruct MCP to overwrite the dumpfile using the REUSE_DUMPFILE clause.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST REUSE_DUMPFILES=YES  METRICS=YES LOGFILE=test.log

     

    SAMPLE – This is used to specify a percentage of data rows to be exported in the dumpfile set.

    It accepts values in the form:- SAMPLE=[[schema_name.]table_name:]sample_percent

    where sample_percent is the percentage of data to be exported.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST SAMPLE=60 LOGFILE=test.log

     

    SCHEMAS – This is used to specify the comma separated list of schemas that you want to be exported.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST1,TEST2 SAMPLE=60 LOGFILE=test.log

     

    STATUS – As we read in the article Queries to Monitor Datapump Jobs, STATUS can be used from another expdp session to get the current picture of all the Datapump Processes. But if you want the STATUS output to be print in the same session from where export operation is being initiated, you can use the command line argument STATUS.

    It accepts values as:- STATUS=[integer]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST LOGFILE=test.log STATUS=100

    With the above command the information of STATUS clause would be printed in the standard output after every 100 seconds.

     

    TABLES – As the name suggests this is used to specify if you want to export one or more set of tables and partitions/sub-partitions.

    It accepts values in the form:- TABLES=[schema_name.]table_name[:partition_name] [, …]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp TABLES=TEST.EMP:TEST2:DEPT LOGFILE=test.log METRICS=Y

     

    TABLESPACES – This clause is used to specify the list of tablespaces that you want to export in tablespace mode. In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded. If a table is unloaded, then its dependent objects are also unloaded. Both object metadata and data are unloaded. If any part of a table resides in the specified set, then that table and all of its dependent objects are exported.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp TABLESPACES=TEST_TBS1,TEST_TBS2 LOGFILE=test.log METRICS=Y

     

    TRANSPORT_FULL_CHECK – When this parameter is set to YES, the export job verifies that there are no dependencies between those objects inside the transportable set and those outside the transportable set.

    For example, if a table is inside the transportable set but its index is not, then a failure is returned and the export operation is terminated. Similarly, a failure is also returned if an index is in the transportable set but the table is not.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp TABLESPACES=TEST_TBS1  TRANSPORT_FULL_CHECK=YES LOGFILE=test.log METRICS=Y

     

    TRANSPORT_TABLESPACES – This parameter is used to specify a list of tablespace names for which object metadata will be exported from the source database into the target database.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp TRANSPORT_TABLESPACES=TEST_TBS1  TRANSPORT_FULL_CHECK=YES LOGFILE=test.log METRICS=Y

    TRANSPORTABLE – Specifies whether the transportable option should be used during a table mode export (specified with the TABLES parameter) to export metadata for specific tables, partitions, and subpartitions.

    It accepts value as:- TRANSPORTABLE = [ALWAYS | NEVER]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp TABLES=TEST.EMP:TEST2:DEPT TRANSPORTABLE=ALWAYS LOGFILE=test.log

     

    VERSION – This clause is used to create a dumpfile set that is compatible with an older release of Oracle Database.

    It accepts values as:- VERSION=[COMPATIBLE | LATEST | version_string]

    • COMPATIBLE: The version of the metadata corresponds to the database compatibility level.
    • LATEST: The version of the metadata corresponds to the database release.
    • version_string: Any specific database release can be specified.

    So if you want to export a dump from 11.2.0.3 Oracle DB version and to make dumpfile compatible with 10.2.0.4 version:-

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST  VERSION=10.2 METRICS=YES LOGFILE=test.log

     

    VIEWS_AS_TABLES – This clause is used if you want to export a view as a table. Data Pump exports a table with the same columns as the view and with row data fetched from the view. Data Pump also exports objects dependent on the view, such as grants and constraints.

    It accepts values in the form:- VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], …

    • schema_name: The name of the schema in which the view resides.
    • view_name: The name of the view to be exported as a table.
    • table_name: The name of a table to serve as the source of the metadata for the exported view.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp VIEW_AS_TABLES=TEST:EMP_V,TEST2:DEPT LOGFILE=test.log

     

    ABORT_STEP – Used to stop the job after it is initialized, it can be used if you want to test a script or to populate master table with data from the dumpfile.

    It accepts values as:- ABORT_STEP=[n | -1],

    n — If the value is zero or greater, then the export operation is started and the job is aborted at the object that is stored in the master table with the corresponding process order number.

    -1 — If the value is negative one (-1) then abort the job after setting it up, but before exporting any objects or data.

    1

    expdp us0er/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST ABORT_STEP=-1 LOGFILE=test.log

     

    CONTENT – Lets you filter out what to export, this is basically used when you want METADATA to be exported and imported across to a different database, to copy the structure.

    It accepts values as:- CONTENT=[ALL | DATA_ONLY | METADATA_ONLY]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST CONTENT=METADATA_ONLY LOGFILE=test.log

     

    ACCESS_METHOD – Hope you have gone through Oracle datapump Architecture & Internals as to understand this further its manadatory to know the architecture and data movement methodologies of datapump APIs.

    As you know that there are 3 choices available for data movement in datapump, using ACCESS_METHOD clause you can instruct MCP to choose a method regardless of the other factors.

    It accepts values as:- ACCESS_METHOD=[AUTOMATIC | DIRECT_PATH | EXTERNAL_TABLE]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST ACCESS_METHOD=EXTERNAL_TABLE LOGFILE=test.log

     

    ATTACH – This clause is to attach a new datapump session to any existing JOB. This is used if you want to check STATUS of a running job or KILL/RESUME/STOP etc a JOB.

    It accepts values as:- ATTACH= [job_name]

    The JOB Name can be fetched from DBA_DATAPUMP_JOBS view.

    1

    expdp ATTACH=TEST.TEST_IMPORT_SCHEMA_02

     

    COMPRESSION– To use compression in the dump file sets this clause is used.

    It accepts values as:- COMPRESSION=[ALL | DATA_ONLY | METADATA_ONLY | NONE]

    • ALL: enables compression for the entire export operation.
    • DATA_ONLY: Enables compression for all data being written to the dump file.
    • METADATA_ONLY: Enables compression for all metadata being written to the dump file.
    • NONE: Disables compression for the entire export operation.

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST COMPRESSION=DATA_ONLY LOGFILE=test.log

     

    COMPRESSION_ALGORITHM – To specify the compression algorithm to be used.

    It accepts values as:- COMPRESSION_ALGORITHM=[BASIC], LOW, MEDIUM and HIGH

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST COMPRESSION=DATA_ONLY COMPRESSION_ALGORITHM=HIGH LOGFILE=test.log

     

    DIRECTORY – Used to specify the Oracle directory where MCP would write the dumpfiles, thus the user invoking the expdp should have read/write access on this Oracle directory.

     

    DUMPFILE – This is the dumpfile name that is created at the DIRECTORY. Variable %U can be used when assigning the dumpfile name, it is required if you use FILESIZE or PARALLEL parameters.

     

    LOGFILE – This clause is used to specify the logfile name which is created in the directory specified by DIRECTORY clause.

    By default the dumpfile name of export job, if the clause is not specified is export.log.

     

    ENCRYPTION – This is used to encrypt part or all of the dump file.

    It accepts values as:- ENCRYPTION = [ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST ENCRYPTION=data_only ENCRYPTION_PASSWORD=T3sT LOGFILE=test.log

     

    ENCRYPTION_ALGORITHM – Specifies which cryptographic algorithm should be used to perform the encryption.

    It accepts values as:- [AES128 | AES192 | AES256]

    1

    expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST ENCRYPTION=data_only ENCRYPTION_PASSWORD=T3sT ENCRYPTION_ALGORITHM=AES128 LOGFILE=test.log



Equivalent exp & expdp parameters:
The below parameters are equivalent parameters between exp & expdp:
exp Command
expdp Command
FEEDBACK STATUS
FILE DUMPFILE
LOG LOGFILE
OWNER SCHEMAS
TTS_FULL_CHECK TRANSPROT_FULL_CHECK


New parameters in expdp Utility

ATTACH Attach the client session to existing data pump jobs
CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)
DIRECTORY Location to write the dump file and log file.
ESTIMATE Show how much disk space each table in the export job consumes.
ESTIMATE_ONLY It estimate the space, but does not perform export
EXCLUDE List of objects to be excluded
INCLUDE List of jobs to be included
JOB_NAME Name of the export job
KEEP_MASTER Specify Y not to drop the master table after export
NETWORK_LINK Specify dblink to export from remote database
NOLOGFILE Specify Y if you do not want to create log file
PARALLEL Specify the maximum number of threads for the export job
VERSION DB objects that are incompatible with the specified version will not be exported.
ENCRYPTION_PASSWORD The table column is encrypted, then it will be written as clear text in the dump file set when the password is not specified. We can define any string as a password for this parameter.
COMPRESSION Specifies whether to compress metadata before writing to the dump file set. Values are: ALL, (METADATA_ONLY), DATA_ONLY and NONE.
SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. The sample_percent indicates the probability that a block of rows will be selected as part of the sample.
REMAP_DATA = Used to Mask (hide) data



Equivalent imp & impdp parameters
These below parameters are equivalent parameters between imp & impdp
imp Command
impdp Command
DATAFILES TRANSPORT_DATAFILES
DESTROY REUSE_DATAFILES
FEEDBACK STATUS
FILE DUMPFILE
FROMUSER SCHEMAS, REMAP_SCHEMAS
IGNORE TABLE_EXISTS_ACTION(SKIP,APPEND,TRUNCATE,REPLACE)
INDEXFILE, SHOW SQLFILE
LOG LOGFILE
TOUSER REMAP_SCHEMA


New parameters in impdp Utility
FLASHBACK_SCN Performs import operation that is consistent with the SCN specified from the source database. Valid only when NETWORK_LINK parameter is used.
FLASHBACK_TIME Similar to FLASHBACK_SCN, but oracle finds the SCN close to the time specified.
NETWORK_LINK Performs import directly from a source database using database link name specified in the parameter. The dump file will be not be created in server when we use this parameter. To get a consistent export from the source database, we can use the FLASHBACK_SCN or FLASHBACK_TIME parameters. These two parameters are only valid when we use NETWORK_LINK parameter.
REMAP_DATAFILE Changes name of the source DB data file to a different name in the target.
REMAP_SCHEMA Loads objects to a different target schema name.
REMAP_TABLESPACE Changes name of the source tablespace to a different name in the target.
TRANSFORM We can specify that the storage clause should not be generated in the DDL for import. This is useful if the storage characteristics of the source and target database are different. The valid values are SEGMENT_ATTRIBUTES, STORAGE. STORAGE removes the storage clause from the CREATE statement DDL, whereas SEGMENT_ATTRIBUTES removes physical attributes, tablespace, logging, and storage attributes.
TRANSFORM = name:boolean_value[:object_type], where boolean_value is Y or N.
For instance, TRANSFORM=storage:N:table
ENCRYPTION_PASSWORD
It is required on an import operation if an encryption password was specified on the export operation.
CONTENT, INCLUDE, EXCLUDE
are same as expdp utilities.
REMAP_DATA = Used to Mask (hide) data


Data Masking with Oracle Data Pump
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

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 &


Some Examples:

In all this cases I 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.

Oracle recommends the following settings to improve performance.
Disk_Asynch_io= true
Db_block_checking=false
Db_block_checksum=false

Additionally, the number of processes and sessions allowed to the database must be set to high, to allow for maximum parallelism.


More Examples
To export only a few specific objects--say, function LIST_DIRECTORY and procedure DB_MAINTENANCE_DAILY--you could use
expdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE:\"=\'DB_MAINTENANCE_DAILY\'\",FUNCTION:\"=\'LIST_DIRECTORY\'\"

This dumpfile serves as a backup of the sources. You can even use it to create DDL scripts to be used later. A special parameter called SQLFILE allows the creation of the DDL script file.
This instruction creates a file named procs.sql in the directory specified by DPDATA1, containing the scripts of the objects inside the export dumpfile. This approach helps you create the sources quickly in another schema.
impdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql

Export/Import a few tables:
expdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
impdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:
expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
impdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

The REMAP_TABLESPACE in the impdp sencence allows you to move the objects from one tablespace to another one.
impdp system SCHEMAS=SCOTT directory=EXPORTPATH DUMPFILE=SCOTT.dmp LOGFILE=imp.log REMAP_TABLESPACE=FGUARD_DATA:FG_DATA

You can also use several REMAP_TABLESPACE clauses in the impdp sencence:
impdp system SCHEMAS=SCOTT directory=EXPORTPATH DUMPFILE=SCOTT.dmp LOGFILE=imp.log REMAP_TABLESPACE=FGUARD_DATA:FG_DATA remap_tablespace=FGUARD_INDX:FG_INDX

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:
expdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log
impdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:
expdp scott/tiger schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log
Each thread creates a separate dumpfile, so the parameter dumpfile should have as many entries as the degree of parallelism.
Note how the dumpfile parameter has a wild card %U, which indicates the files will be created as needed and the format will be SCOTT_nn.dmp, where nn starts at 01 and goes up as needed.

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export:
expdp scott/tiger schemas=SCOTT include=TABLE:\"IN (\'EMP\', \'DEPT\')\" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
expdp scott/tiger schemas=SCOTT exclude=TABLE:\"= \'BONUS\'\" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log


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

Another example:
We want to check how much a specific session (sid=9) needs to perform in order to finish. So using the PRINT_TABLE function described in AskTom.com you we can do the following:
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
Let's examine each of these columns carefully. There may be more than one long running operation in the session—especially because the view contains the history of all long running operations in previous sessions. The column OPNAME shows that this record is for "Transaction Rollback," which points us in the right direction. The column TIME_REMAINING shows the estimated remaining time in seconds, described earlier and the column ELAPSED_SECONDS shows the time consumed so far. So how does this table offer an estimate of the remaining time? Clues can be found in the columns TOTALWORK, which shows the total amount of "work" to do, and SOFAR, which shows how much has been done so far. The unit of work is shown in column UNITS. In this case, it's in blocks; therefore, a total of 10,234 blocks have been rolled back so far, out of 20,554. The operation so far has taken 77 seconds. Hence the remaining blocks will take: 77 * ( 10234 / (20554-10234) ) ≈ 77 seconds But you don't have to take that route to get the number; it's shown clearly for you. Finally, the column LAST_UPDATE_TIME shows the time as of which the view contents are current, which will serve to reinforce your interpretation of the results.


Which object types are left?
SQL> select unique object_type_seqno, object_type
       from system.sys_import_full_01
       where process_order > 0 AND processing_state = ‘R’
         and processing_status = ‘C’;

OBJECT_PATH_SEQNO OBJECT_TYPE

----------------- -----------------------------
              103 PROCEDURE
              119 ALTER_PROCEDURE
              137 VIEW



What is left for the current object?
SQL> select object_schema, object_name
       from system.sys_import_full_01
       where process_order > 0 and processing_state = 'R'
         and processing_status = 'C'
         and
object_path_seqno = 103;

OBJECT_SCHEMA OBJECT_NAME
-------------------- --------------------
                  HR ADD_JOB_HISTORY
                  HR SECURE_DML





Another Example using PL/SQL and the Data Pump API:
The Data Pump API, DBMS_DATAPUMP, provides a high-speed mechanism to move the data from one database to another. The structure used in the client interface of this API is a job handle. Job handle can be created using the OPEN or ATTACH function of the DBMS_DATAPUMP package. Other DBA sessions can attach to a job to monitor and control its progress so that remote DBA can monitor the job that was scheduled by an on-site DBA.
The following steps list the basic activities involved in using Data Pump API.
1.      Execute DBMS_DATAPUMP.OPEN procedure to create job.
2.      Define parameters for the job like adding file and filters etc.
3.      Start the job.
4.      Optionally monitor the job until it completes.
5.      Optionally detach from job and attach at later time.
6.      Optionally, stop the job
7.      Restart the job that was stopped.

declare
h1   NUMBER;
 begin
  begin
      h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'IMPORTGO', version => 'COMPATIBLE');
  end;
  begin
     dbms_datapump.set_parallel(handle => h1, degree => 1);
  end;
  begin
     dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATAPUMP', filetype => 3);
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
  end;
  begin
     dbms_datapump.add_file(handle => h1, filename => 'FRAUDGUARD.dmp', directory => 'DATAPUMP', filetype => 1);
  end;
  begin
     dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_SCHEMA', old_value => 'FRAUDGUARD', value => 'PROD_FNT_FG83540');
  end;
  begin
     dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_TABLESPACE', old_value => 'FGUARD_DATA', value => 'FG_DATA');
  end;
  begin
     dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_TABLESPACE', old_value => 'FGUARD_ARCH_DATA', value => 'FG_DATA');
  end;
  begin
     dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_TABLESPACE', old_value => 'FGUARD_INDX', value => 'FG_INDX');
  end;
  begin
     dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_TABLESPACE', old_value => 'FGUARD_ARCH_INDX', value => 'FG_INDX');
  end;
  begin
     dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''FRAUDGUARD'')');
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'TRUNCATE');
  end;
  begin
     dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
  end;
  begin
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
  end;
  begin
     dbms_datapump.detach(handle => h1);
  end;
end;
/



More Examples HERE