Data pump in oracle utility with example

Summary 

In this article we will learn how to create data pump directory , grant permission to the user, how to export a single table as well as multiple tables using export data pump (expdp) in oracle utility,how to import the exported table using import data pump (impdp) in oracle utility.

Create directory data pump in oracle

To create the data pump directory we follow these below steps.

First we need to create a directory in my case “dp” under the /oracle .To know how to create the directory in os level click this article.

Directory Management Of Linux/unix with practicalguide

Then we go to the sql promt and create “data_pump “ directory and link to it “/oracle/dp”

Grant permission on directory data pump in oracle

We peform the export and import activity using scott user .To do these operation we need to grant the permission to the scott user.

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

How to use Export data pump utility in oracle 

We have a particular table named “staff” in the scott schema. we want to export staff table using scott user to the data_pump directory. we can export single table as well as multiple tables using tables data pump parameter.

Export single table using data pump utility 

$ expdp scott/tiger directory=data_pump dumpfile=staff.dmp logfile=staff.log tables=staff;

Export: Release 11.2.0.1.0 - Production on Mon Aug 6 08:38:51 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=data_pump dumpfile=staff.dmp logfile=staff.log tables=staff

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."STAFF"                             5.929 KB       2 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /oracle/dp/staff.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 08:38:56

Export and Import table on oracle 11g database with examples using expdp  and impdp utility

Export multiple  tables using data pump utility 

Suppose we have another table called”dept” to export multiple tables we execute the below command.

$ expdp scott/tiger directory=data_pump dumpfile=staff.dmp logfile=staff.log tables=staff,dept;

 

How to use Import data pump utility in oracle 

Now we import the exported table that is “staff “ in the scott schema. To do this operation we execute the below command.

Import  single table using data pump utility 

$ impdp scott/tiger directory=data_pump dumpfile=staff.dmp tables=staff;

Import: Release 11.2.0.1.0 - Production on Mon Aug 6 08:46:03 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=data_pump dumpfile=staff.dmp tables=staff

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."STAFF"                             5.929 KB       2 rows

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 08:46:05

Import  multiple tables using data pump utility 

To import the multiple tables we execute the below command.

$ impdp scott/tiger directory=data_pump dumpfile=staff.dmp tables=staff,dept;

Conclusion 

In this article we have  learned how to create data pump directory , grant permission to the user, how to export a single table as well as multiple tables using export data pump (expdp) in oracle utility,how to import the exported table using import data pump (impdp) in oracle utility.

Please Donate To Bitcoin Address: [[address]]

Donation of [[value]] BTC Received. Thank You.

[[error]]

SHARE

Admin

• 
• 
• 
• 
•