Netsoftmate

Data Pump expdp/impdb Scenarios

Written by

Netsoftmate

in

Overview

Datapump introduce in 10g which is very powerful utility to perform the both load and unload data using external dump files.Oracle Data Pump technology consists of two components: the Data Pump Export utility, to unload data objects from a database, and the Data Pump Import utility, to load data objects into a database. You access the two Data Pump utilities through a pair of clients called expdp and impdp.As their names indicate, the first of these corresponds to the Data Pump Export utility and the latter
to the Data Pump Import utility. You can control both Data Pump Export and Import jobs with the help of several parameters.


In this article we will demonstrate different Data Pump (expdp/impdb) scenarios.

Prerequisites

  • Creating a Database Directory
  • Create a directory named expdp_dir and
    specifies that it is to map to the filesystem/location and physical location on
    disk:
SQL> create
directory expdp_dir as ‘ /orahm/app/oracle/admin/db01/dpdump’;

  • Granting Access to the Directory
  • Grant
    permissions on the database-directory object to a user that wants to use Data
    Pump:-
SQL> grant
read, write on directory expdp_dir to sys;

Table level


Export Source DB

SQL> select
* from dba_directories;
OWNER       DIRECTORY_NAME        DIRECTORY_PATH
—————————–
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
logfile=exp-tab.log

scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp  oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$impdp
directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
logfile=exp-tab.log



Schema level
Below example
to take the schema level export and import.


Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
$expdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log

scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


$impdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log

Check invalid
object and compile if required.

select
object_name from dba_objects where owner=’ABC’ and status=’INVALID’ ;
compiling
invalid object.

ALTER PROCEDURE
<PROCEDURE_NAME> COMPILE;

Database level
Below example
for full DB export and import.


Export Source DB

SQL> select
* from dba_directories;
OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
Check the
tablespace name and size to match same on target db

select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000)
“Total (MB)”,
ROUND(b.BYTES/1024000)
“Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% USED”,
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% FREE”
from
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES ,
max(BYTES)
largest
from    dba_free_space
group   by TABLESPACE_NAME
)b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and
a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;

$expdp
directory= DATA_PUMP_DIR full=y dumpfile=exp-full.dmp logfile=exp-full.log

scp /orahm/app/oracle/admin/db01/dpdump/
exp-full.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/


Import Target DB


Make target db
contain all the tablespace and available free space to accommodate the source
object into target DB

Sql> select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000)
“Total (MB)”,
ROUND(b.BYTES/1024000)
“Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% USED”,
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% FREE”
from
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES ,
max(BYTES)
largest
from    dba_free_space
group   by TABLESPACE_NAME
)b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and
a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;

$impdp
directory= DATA_PUMP_DIR full=y dumpfile=exp-full.dmp logfile=imp-full.log

Check the
invalid object and compile.
Sql>select
object_name,owner,status from dba_objects where status=’INVALID’;
Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Sql>select
object_name,owner,status from dba_objects where status=’INVALID’;

Using Parameter file
Below export
and import example using parameter file.


Export Source DB

SQL> select
* from dba_directories;
OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
Check the
tablespace name and size to match same on target db

select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000)
“Total (MB)”,
ROUND(b.BYTES/1024000)
“Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% USED”,
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% FREE”
from
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES ,
max(BYTES)
largest
from    dba_free_space
group   by TABLESPACE_NAME
)b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and
a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Create
parameter file as below under data pump directory.

vi full_db.par
directory=DATA_PUMP_DIR
full=y
dumpfile=exp-full.dmp
logfile=exp-full.log

Take export
using par file.
$ expdp
parfile=full_db.par

scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp  oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/


Import Target DB


Make target db
contain all the tablespace and available free space to accommodate the source
object into target DB

Sql> select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000)
“Total (MB)”,
ROUND(b.BYTES/1024000)
“Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% USED”,
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% FREE”
from
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES ,
max(BYTES)
largest
from    dba_free_space
group   by TABLESPACE_NAME
)b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and
a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;
                               
Create
parameter file as below under data pump directory.


vi full_db.par
directory=DATA_PUMP_DIR
full=y dumpfile=exp-full.dmp
logfile=imp-full.log


Perform import using parameter file.
$impdp parfile=full_db.par

Check the
invalid object and compile.
Sql>select
object_name,owner,status from dba_objects where status=’INVALID’;
Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Sql>select
object_name,owner,status from dba_objects where status=’INVALID’;


Data Pump Export/Import more examples

  • Using parallel option
Using parallel
option we can make export faster. It generate more dump files depends on
parallel option during export.


Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile=exp-schema_%Udmp logfile= exp-schmea.log
parallel=2

scp /orahm/app/oracle/admin/db01/dpdump/exp-schema_%U.dmp
oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


$impdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema_%U.dmp logfile=imp-schema.log

Check invalid
object and compile if required.

select
object_name from dba_objects where owner=’ABC’ and status=‘INVALID’;
Compiling
invalid object.

ALTER PROCEDURE
<PROCEDURE_NAME> COMPILE;

  • Using compress option
Using compress
option we can reduce the size of dump files.


Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR compression=all schemas=ABC dumpfile= exp-schema.dmp
logfile= exp-schmea.log

scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


$impdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log

Check invalid
object and compile if required.

select
object_name from dba_objects where owner=’ABC’ and status=‘INVALID’;
Compiling
invalid object.

ALTER PROCEDURE
<PROCEDURE_NAME> COMPILE;


Creating DDL file
Using Sqlfile
option we can only extract the DDL without data.


Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
logfile=exp-tab.log sqlfile=ddl-script.sql

scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp
ddl-script.sql oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


Import only DDL
$impdp
directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
logfile=exp-tab.log sqlfile=ddl-script.sql

  • Cloning user
    (remap_schema)
Using
Remap_schema option we can import the object from one schema to other schema.


Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR compression=all schemas=ABC dumpfile= exp-schema.dmp
logfile= exp-schmea.log

scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


$impdp
directory= DATA_PUMP_DIR remap_schema=ABC:XYZ dumpfile= exp-schema.dmp logfile=imp-schema.log

Check invalid
object and compile if required.

select
object_name from dba_objects where owner=’XZY’ and status=‘INVALID’;

Compiling
invalid object.

ALTER PROCEDURE
<PROCEDURE_NAME> COMPILE;

  • Cloning table (remap_table)
Remap_table
option is to create the table with new name during import to avoid overwriting
existing table in target DB.


Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR tables=ABC.table1 dumpfile=exp-tab.dmp logfile=exp-tab.log

scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp
oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


Remap table to
new name.


$impdp
directory= DATA_PUMP_DIR tables=ABC.table1 remap_table= ABC.table1:table2 dumpfile=exp-tab.dmp
logfile=exp-tab.log

  • Importing when objects Already Exist
Using the TABLE_EXISTS_ACTION
option we can import the object if the object already exist on target.

TABLE_EXISTS_ACTION=[SKIP
| APPEND | TRUNCATE | REPLACE]
SKIP leaves the
table as is and moves on to the next object. This is not a valid option if the CONTENT
parameter is set to DATA_ONLY.
APPEND loads
rows from the source and leaves existing rows unchanged.
TRUNCATE
deletes existing rows and then loads rows from the source.
REPLACE drops
the existing table and then creates and loads it from the source. This is not a
valid option if the CONTENT parameter is set to DATA_ONLY.

Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log

scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import


$impdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log
table_exists_action==[SKIP | APPEND | TRUNCATE | REPLACE]

Check invalid
object and compile if required.

select
object_name from dba_objects where owner=’ABC’ and status=’INVALID’ ;
compiling
invalid object.

ALTER PROCEDURE
<PROCEDURE_NAME> COMPILE;

  • Exclude option
EXCLUDE
parameter is used, all objects except those specified by it will be included in
the export/import


Export Source DB

SQL> select
* from dba_directories;
OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
Check the tablespace
name and size to match same on target db

select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000)
“Total (MB)”,
ROUND(b.BYTES/1024000)
“Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% USED”,
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% FREE”
from
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES ,
max(BYTES)
largest
from    dba_free_space
group   by TABLESPACE_NAME
)b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and
a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;

Create
parameter file as below under data pump directory.

vi full_db.par
directory=DATA_PUMP_DIR
full=y
dumpfile=exp-full.dmp
logfile=exp-full.log
exclude=schema:”IN
(‘SYS’,’SYSTEM’,’ANONYMOUS’,’DBSNMP’,’DIP’,’EXFSYS’,’ORACLE_OCM’,’OUTLN’,’WMSYS’,’XDB’)”

Take export
using par file.

$ expdp
parfile=full_db.par

scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


Make target db
contain all the tablespace and available free space to accommodate the source
object into target DB

Sql> select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000)
“Total (MB)”,
ROUND(b.BYTES/1024000)
“Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% USED”,
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
“% FREE”
from
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES)
BYTES ,
max(BYTES)
largest
from    dba_free_space
group   by TABLESPACE_NAME
)b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and
a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;
                               
Create
parameter file as below under data pump directory.

vi full_db.par
directory=DATA_PUMP_DIR
full=y
dumpfile=exp-full.dmp
logfile=imp-full.log

Perform import using parameter file.
$impdp parfile=full_db.par

Check the
invalid object and compile.
Sql>select
object_name,owner,status from dba_objects where status=’INVALID’;
Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Sql>select
object_name,owner,status from dba_objects where status=’INVALID’;

  • Include option
INCLUDE
parameter is used, only those objects specified by it will be included in the
export/import.


Export Source DB

SQL> select
* from dba_directories;

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR schemas=ABC 
include=TABLE:”IN(“EMP’,’DEPT’) dumpfile= exp-schema.dmp logfile= exp-schmea.log

scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


$impdp
directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log

Check invalid
object and compile if required.

select
object_name from dba_objects where owner=’ABC’ and status=’INVALID’ ;
compiling
invalid object.

ALTER PROCEDURE
<PROCEDURE_NAME> COMPILE;

  • Creating a Consistent
    Export
CONSISTENT=Y
parameter to indicate the export should be consistent to a point in time


Export Source DB


SQL> select
* from dba_directories;
OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

$expdp
directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
logfile=exp-tab.log consistent=y

scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp  oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

Import Target DB


$impdp
directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
logfile=exp-tab.log

  • Network based import
NETWORK_LINK
parameter identifies a database link to be used as the source for a network
export/import.


Export Source DB


SQL> select
* from dba_directories;
OWNER       DIRECTORY_NAME        DIRECTORY_PATH
————————————————————————-
SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

Create the db
link on on local db which is point to source db for export task.It will export
the source db and put the dumpfile in dump directory

sql>CREATE DATABASE
LINK remote_expdp CONNECT TO sys IDENTIFIED BY sys123 USING ‘DB01’;

$expdp
directory= DATA_PUMP_DIR tables=owner.table_name network_link=remote_expdp dumpfile=exp-tab.dmp
logfile=exp-tab.log

Import Target DB


Create the db
link on local DB which is point to source db. Here it will directly import the
data using netwok link option without dumpfile of source db.

sql>CREATE DATABASE
LINK remote_impdp CONNECT TO sys IDENTIFIED BY sys123 USING ‘DB01’;

$impdp
directory= DATA_PUMP_DIR tables=owner.table_name logfile=exp-tab.log network_link=remote_impdp


Conclusion


In this article we have learnt different Data Pump (expdp/impdb) scenarios.

Comments

2 responses to “Data Pump expdp/impdb Scenarios”

  1. Ahmed Avatar
    Ahmed

    Very Helpful to understand

    Reply
  2. RAJ SHIVAM Avatar
    RAJ SHIVAM

    Thank you so much for explaining the export import very well

    It is very helpful..

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts