top of page

Oracle Data Pump - expdp, impdp

Efficiently export and import data using Oracle Data Pump utilities.

Oracle Data Pump is a fast data movement utility provided by Oracle. It’s an upgrade to old export and import utility. The Data Pump utility has been built from scratch and it has a completely different architecture.



Advance Topics



Create Data Pump Directory


The first step in Oracle Data Pump is to create an OS level directory which will be used by Oracle for performing exports and imports. Create directory at OS level

mkdir -p /u02/dp_exp_dir

Create directory inside the database

SQL> create directory datapump as '/u02/dp_exp_dir';

Grant permissions on directory

SQL> grant read,write on directory datapump to scott; 

View directory information

SQL> select * from dba_directories;

Get help on expdp or impdp utility

expdp help=y
impdp help=y


Table Export and Import


Take table level export

expdp directory=datapump dumpfile=emp_bkp.dmp logfile=emp_bkp.log tables='SCOTT.EMP'

Import table where source and target schema are same

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP'

Import table to another schema

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:HR'

Import tables to another tablespace (only in datapump)

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:HR' remap_tablespace='USERS:MYTBS'

Import table to a different name or rename table or remap_table

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_table='SCOTT.EMP:HR.EMPLOYEE'

Import only the rows from an exported table without loading table any table definitions

$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' content=DATA_ONLY


Schema Export and Import


Take schema level export

$ expdp directory=datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'

Import source schema objects into same schema on target

$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:SCOTT'

Import source schema objects into a different schema on target

$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:HR'


Rows Export and Import


Take row level export

$ expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables='SCOTT.EMP' query=\"where deptno=10\"

Import rows where source and target schema are same

$ impdp directory=datapump dumpfile=emprows_bkp.dmp logfile=imp_emprows.log tables='SCOTT.EMP'


Full Database Export and Import


Take database level export

$ expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

Import full database

-- On source
SQL> select name from v$tablespace;

-- On target
SQL> select name from v$tablespace;

-- Create missing tablespaces on target
-- Make sure target tablespace has enough free space
-- Drop all non-oracle schemas (done during refresh)
-- DROP USER <username> CASCADE;

$ impdp directory=datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y


Data Pump Import Over Network


When you try to move large tables or schema between two Oracle databases, datapump export might take lot of disk space. The exported dump files may take lot of space on the disk. The best workaround is to use dblink with Oracle datapump to move data from one oracle database to another.


Note: when you use datapump with dblink, there are no dumpfiles created on source. The data is transferred from one database to another over network

Add source database TNS entry into tnsnames.ora of the target database

devdb =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = devdb)
 )
)

On target, we need to create a database link using the TNS entry created above

create database link SOURCE_DB connect to scott identified by tiger  using 'devdb';

It's time to import source schema on target database via db link. Run below command on target database to start import

create or replace directory MY_DUMP_DIR as '/u01/dump_files';

impdp directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log 
network_link=SOURCE_DB remap_schema=scott:hr

To import multiple schemas, make sure to use a DBA user (sys) to perform this action

impdp sys directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log 
network_link=SOURCE_DB schemas=IJS,scott,hr


Data Pump Performance Tuning


You can always use DIRECT=y parameter to perform faster exports and imports. You can also use PARALLEL parameter to start multiple export and import process for faster performance.

Make sure to use %U with the dumpfile name so multiple dumpfiles can be read/write simultaneously
expdp directory=DUMP_DIR DIRECT=y dumpfile=SCOTT_%U.dmp logfile=expdp_SCOTT.log schemas=SCOTT parallel=4

impdp directory=DUMP_DIR DIRECT=y dumpfile=SCOTT_%U.dmp logfile=impdp_SCOTT.log schemas=SCOTT parallel=4


EXPDP PAR File Example


Data Pump jobs can be automated using PAR file. You basically create one par file which contains all the export or import parameters and just call the par file at expdp utility

vi exp.par

Username=scott/tiger
tables=scott.emp
directory=EXP_DIR
dumpfile=QUERY_EXP_%U.dmp
logfile=QUERY_EXP.log
parallel=7

And! Its very simple to call the above export PAR file

expdp parfile=exp.par
You can specify any extension but it is recommended to use .par


Schedule Data Pump Export in crontab


Create a file which contains expdp script

vi daily_export.sh

export DATE=$(date +%m_%d_%y_%H_%M)
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

$ORACLE_HOME/bin/expdp username/password@sid directory=export_dir dumpfile=backup_$DATE.dmp logfile=backup_$DATE.log full=y

Give permissions to execute on above file

chmod 755 daily_export.sh

Schedule export under crontab

crontab –e –u oracle

0020***/home/oracle/backup_script

Data Pump Export Progress %


When you run a data pump export in the background and want to know the progress status, use below query to get the percentage (%) completion of the export process

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


DBMS_DATAPUMP Export/Import From SQL*Plus


You can invoke DataPump export / import inside SQL*Plus even if you do not have access to OS level EXPDP / IMPDP utility. It replaces traditional export/import utilities and provides a much more efficient way to move large amounts of data. Let's start the Export and Import using DBMS_DATAPUMP PL/SQL API.


Export Schema Using DBMS_DATAPUMP


We will export the table from testuser1 from ORCL Database to testuser1 to PROD Database. Create a directory for dumps and log


mkdir -p /u01/dp

Create an Oracle directory object pointing to the physical location

create or replace directory test_dir AS '/u01/dp';
grant read, write on directory test_dir to testuser1

Let's export EMP table from testuser1 schema

declare
  l_dp_handle       number;
begin
  -- Open a table export job.
  l_dp_handle := dbms_datapump.open(
    operation   => 'EXPORT',
    job_mode    => 'TABLE',
    remote_link => NULL,
    job_name    => 'TESTUSER1_EMP_EXPORT',
    version     => 'LATEST');

  -- Specify the dump file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'TESTUSER1_EMP.dmp',
    directory => 'TEST_DIR');

  -- Specify the log file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'expdpTESTUSER1_EMP.log',
    directory => 'TEST_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- Specify the table to be exported, filtering the schema and table.
  dbms_datapump.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''TESTUSER1''');

  dbms_datapump.metadata_filter(
    handle => l_dp_handle,
    name   => 'NAME_EXPR',
    value  => '= ''EMP''');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
end;
/

dbms datapump export table pl sql procedure

Let's review the log file to verify the export status and be sure.

dbms datapump export table log

Import Schema Using DBMS_DATAPUMP


Create an Oracle directory object pointing to the physical location on target server

create or replace directory test_dir AS '/u01/dp';
grant read, write on directory test_dir to testuser1;

Let's import EMP table in testuser1 schema and change table name to EMP2

declare
  l_dp_handle       number;
begin
  -- Open a schema import job.
  l_dp_handle := dbms_datapump.open(
    operation   => 'IMPORT',
    job_mode    => 'TABLE',
    remote_link => NULL,
    job_name    => 'TESTUSER1_EMP_IMPORT',
    version     => 'LATEST');

  -- Specify the dump file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'TESTUSER1_EMP.dmp',
    directory => 'TEST_DIR');

  -- Specify the log file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'impdpTESTUSER1_EMP_IMPORT.log',
    directory => 'TEST_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- Perform a REMAP_TABLE from EMP to EMP2.
  dbms_datapump.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_TABLE',
    old_value  => 'EMP',
    value      => 'EMP2');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
end;
/

dbms datapump import table pl sql procedure

Note: As it is a table import, make sure the schema is already created.

Become a top notch dba with DBA Genesis
Start your DBA career today
bottom of page