How to export and import in oracle Database.! Data pump(expdp,impdp)

by

Introduction:-

In this article, we will learn how to export and import database objects using datapump in oracle. Steps to execute Datapump (expdb,impdp).

Data Pump utility to export/import database objects.

Oracle Data Pump is a newer, faster and more flexible alternative to the  traditional “exp” and “imp” utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.


Before going to export/import in oracle database make sure directory exist, if not we will have to create it first.


The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.

SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/oracle/oradata/';

SQL> GRANT READ, WRITE ON DIRECTORY APPS_DIR TO apps;

Query ALL_DIRECTORIES/dba_directories view to display all existing direcotiries.

SQL> select owner,directory_name,directory_path from all_directories;

Table Exports/Imports:-

TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

Parameter tables allow to mention more than one table..

expd apps/apps@UAT tables=EMP_POLICY_MASTER directory=APPS_DIR dumpfile=EMP_POLICY_MASTER.dmp logfile=expdpEMP_POLICY_MASTER.log


impdp apps/apps@UAT tables=EMP_POLICY_MASTER directory=APPS_DIR dumpfile=EMP_POLICY_MASTER.dmp logfile=impdpEMP_POLICY_MASTER.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports:-

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.

expdp apps/apps@UAT schemas=apps directory=APPS_DIR dumpfile=apps.dmp logfile=expdpAPPS.log


impdp apps/apps@UAT schemas=apps directory=APPS_DIR dumpfile=apps.dmp logfile=impdpAPPS.log

Database Exports/Imports:-

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.

expdp system/password@UAT full=Y directory=APPS_DIR dumpfile=DB11g.dmp logfile=expdpDB11g.log

 
impdp system/password@UAT full=Y directory=APPS_DIR dumpfile=DB11g.dmp logfile=impdpDB11g.log

How import table in oracle database…

Before import the table . we will drop the table EMP_POLICY_MASTER.

SQL>drop table EMP_POLICY_MASTER.
$impdp apps/apps tables=EMP_POLICY_MASTER directory=apps_dir dumpfile=EMP_POLICY_MASTER1.dmp file=expdpEMP_POLICY_MASTER1.log


Import: Release 11.2.0.4.0 - Production on Sat Feb 20 15:43:15 2021

 

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

 

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

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

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "file=expdpEMP_POLICY_MASTER1.log" Location: Command Line, Replaced with: "dumpfile=expdpEMP_POLICY_MASTER1.log"

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

Starting "APPS"."SYS_IMPORT_TABLE_01":  apps/******** tables=EMP_POLICY_MASTER directory=apps_dir dumpfile=EMP_POLICY_MASTER1.dmp dumpfile=expdpEMP_POLICY_MASTER1.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "APPS"."EMP_POLICY_MASTER"                  146.5 KB    1310 rows

Job "APPS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Feb 20 15:43:16 2021 elapsed 0 00:00:01

We can see successfully imported the table….

SQL> select count(*) from EMP_POLICY_MASTER;

 

  COUNT(*)

----------

      1310
THANK YOU.........

For another helpful article, you may check it, click here.

Leave a Comment