Oracle Data Pump - expdp, impdp

Oracle Data Pump is a powerful utility that enables efficient and flexible data movement between Oracle databases. It provides high-speed export and import functionality, allowing you to transfer data and metadata across different database instances.

Here's an overview of the key aspects you need to know about Oracle Data Pump export and import:
Create a Datapump Directory
Full Database Export and Import
Schema Export and Import
Table Export and Import
Data Pump Performance Tuning
Data Pump Export and Import Progress %

Create a Datapump Directory

The first step in Oracle Data Pump is to create a directory at the operating system (OS) level that will be used by Oracle datapump for performing exports and imports. Create a directory at OS level:

  mkdir -p /opt/oracle/barode/backup  

Create a directory at the DB level:

  SQL> create directory datapump_export as '/opt/oracle/barode/backup';  

Grant permissions to the db_user for the directory:

 SQL> grant read,write on directory datapump_export to db_user;  

Full Database Export and Import

The "FULL" parameter indicates the requirement for a complete database export. Below is an example of the syntax for exporting and importing the full database.

 expdp system/password@db_instance_name full=Y directory=datapump_export dumpfile=full_db_backup.dmp logfile=full_db_backup.log

 impdp system/password@db_instance_name full=Y directory=datapump_export dumpfile=full_db_backup.dmp logfile=full_db_restore.log
             

The user performing the database export will need the DATAPUMP_EXP_FULL_DATABASE role, while the user performing the import will require the DATAPUMP_IMP_FULL_DATABASE role.

Schema Export and Import

To export or import a specific database schema or multiple schemas, you need to specify the "SCHEMA" parameter. This parameter instructs the datapump to export or import the specified schema or a list of schemas defined by the parameter.

 expdp system/password@db_instance_name schemas=db_user directory=datapump_export dumpfile=db_user_bkp.dmp logfile=db_user_backup.log

 impdp system/password@db_instance_name schemas=db_user directory=datapump_export dumpfile=db_user_bkp.dmp logfile=db_user_restore.log
             

Table Export and Import

The "TABLES" parameter is utilized to specify the tables that need to be exported or imported. Here is an example of the syntax for exporting and importing tables.

 expdp db_user/password@db_instance_name TABLES=MY_TABLE1,MY_TABLE2 directory=datapump_export dumpfile=my_db_tables.dmp logfile=my_db_tables_backup.log

 impdp db_user/password@db_instance_name TABLES=MY_TABLE1,MY_TABLE2 directory=datapump_export dumpfile=my_db_tables.dmp logfile=my_db_tables_restore.log
             

If the source and target database schema names are different, you can use the "REMAP_SCHEMA" parameter to instruct datapump to import the table(s) into another schema.
For example:

 impdp db_user1/password@db_instance_name TABLES=MY_TABLE1,MY_TABLE2 directory=datapump_export remap_schema=DB_USER:DB_USER1 dumpfile=my_db_tables.dmp logfile=my_db_tables_restore.log
             

If you wish to rename a table during the import process, you can use the "REMAP_TABLE" parameter to instruct Oracle Data Pump to change the table's name.
For Example:

 impdp db_user/password@db_instance_name TABLES=MY_TABLE1,MY_TABLE2 remap_table=db_user.MY_TABLE1:NEW_TABLE1,db_user.MY_TABLE2:NEW_TABLE2 directory=datapump_export dumpfile=my_db_tables.dmp logfile=my_db_tables_restore.log
             

If you wish to import only data without creating a table during the import process, you can use the "CONTENT=DATA_ONLY" parameter to instruct Oracle Data Pump to import only the table data. This can be useful if the target table is already present in the schema, and there is a need to only import the data.

 impdp db_user/password@db_instance_name TABLES=MY_TABLE1,MY_TABLE2 content=data_only directory=datapump_export dumpfile=my_db_tables.dmp logfile=my_db_tables_restore.log
             

If you want to change the tablespace name during the import process, you can use the "REMAP_TABLESPACE" parameter to instruct Oracle Data Pump to assign a new tablespace. This option can be used during full, schema-level, or table-level imports.

 impdp db_user/password@db_instance_name TABLES=MY_TABLE1,MY_TABLE2 remap_tablespace=USER_TBS:USER_TBS1 directory=datapump_export dumpfile=my_db_tables.dmp logfile=my_db_tables_restore.log
             

Data Pump Performance Tuning

Data Pump performance can be enhanced by utilizing the "PARALLEL" parameter. This parameter should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to enable the creation or reading of multiple dumpfiles. The same wildcard can be employed during the import process to reference multiple files.
For example:

 expdp system/password@db_instance_name schemas=db_user directory=datapump_export dumpfile=db_user_bkp_%U.dmp paralle=4 logfile=db_user_backup.log

 impdp system/password@db_instance_name schemas=db_user directory=datapump_export dumpfile=db_user_bkp_%U.dmp paralle=4 logfile=db_user_restore.log
             

The number of parallel session depends on the number of CPUs available to support the datapump export and import on the server.

Data Pump Export & import Progress %

To monitor the progress of datapump job you can use the following SQL :

 
 SELECT 
      SID, 
      SERIAL#, 
      USERNAME, 
      CONTEXT, 
      SOFAR, 
      TOTALWORK,
      ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
      FROM V$SESSION_LONGOPS 
  WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;