Data Pump expdp/impdb Scenarios

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.