How to use Data Pump in Oracle:

This article explains how to use Data Pump in Oracle to export/import data to/from the Oracle cluster.

Data pump is a Utility provided by Oracle to Export(EXPDP)/Import(IMPDP) the data to/from the new/old Oracle cluster started from Oracle 10g(10.1), which are performance-wise better as compared to the original Export(EXP) and Import(IMP) utilities.

It supports different modes of loading and unloading the data and objects in the database, which includes: 

  1. full database mode

  2. schema mode

  3. table mode

  4. tablespace mode

  5. transportable tablespace mode.

Example:

Step1. Export the complete HR schema using EXPDP utility.

Step2. Drop the HR schema.

Step3. Import the HR schema using IMPDP utility, from the dump file(hr_dump.dmp) generated in [step1].


1. Export the complete HR schema using EXPDP utility.

[oracle@localhost ~]$ EXPDP USERID=system/manager@orcl DUMPFILE=hr_dump.dmp SCHEMAS=HR


Export: Release 12.2.0.1.0 – Production on Thu Jan 2 22:36:24 2020

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″:  userid=system/******** dumpfile=hr_dump.dmp schemas=HR 

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

. . exported “HR”.”EMPLOYEES” 17.08 KB 107 rows

. . exported “HR”.”LOCATIONS” 8.437 KB 23 rows

. . exported “HR”.”JOB_HISTORY” 7.195 KB 10 rows

. . exported “HR”.”JOBS” 7.109 KB 19 rows

. . exported “HR”.”DEPARTMENTS”  7.125 KB 27 rows

. . exported “HR”.”COUNTRIES” 6.367 KB 25 rows

. . exported “HR”.”TAB1″ 8.351 KB 3 rows

. . exported “HR”.”REGIONS” 5.546 KB 4 rows

Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/orcl/dpdump/hr_dump.dmp

Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Thu Jan 2 22:37:21 2020 elapsed 0 00:00:57


2. Connect to system user and drop the HR schema.

[oracle@localhost ~]$ sqlplus system/manager@orcl

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 2 22:38:25 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 02 2020 22:36:24 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production


SQL> drop user HR cascade;

User dropped.


3. Import the HR schema using IMPDP utility, from the dump file(hr_dump.dmp) generated in [step1] under “/u01/app/oracle/admin/orcl/dpdump/” directory.


Path of this dump file can be found using the below query:

SQL> SELECT * FROM dba_directories WHERE DIRECTORY_NAME like ‘DATA_PUMP_DIR’;


[oracle@localhost ~]$ IMPDP USERID=system/manager@orcl SCHEMAS=HR LOGFILE=partial_import.log DUMPFILE=hr_dump.dmp

Import: Release 12.2.0.1.0 – Production on Thu Jan 2 22:41:04 2020

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded

Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″:  userid=system/******** schemas=HR LOGFILE=partial_import.log DUMPFILE=hr_dump.dmp 

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported “HR”.”EMPLOYEES” 17.08 KB 107 rows

. . imported “HR”.”LOCATIONS”              8.437 KB 23 rows

. . imported “HR”.”JOB_HISTORY”            7.195 KB 10 rows

. . imported “HR”.”JOBS”                   7.109 KB 19 rows

. . imported “HR”.”DEPARTMENTS”            7.125 KB 27 rows

. . imported “HR”.”COUNTRIES”              6.367 KB 25 rows

. . imported “HR”.”TAB1″                   8.351 KB 3 rows

. . imported “HR”.”REGIONS”                5.546 KB 4 rows

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Thu Jan 2 22:41:30 2020 elapsed 0 00:00:25


To import the complete user using IMPDP and database link(which is owned by exporting user), we can use the below command.


IMPDP import_user/password NETWORK_LINK=db_link FULL=Y;


For more details on “DATA PUMP” and it’s version compatibility in oracle visit the link below: https://docs.oracle.com/cd/E11882_01/server.112/e23633/expimp.htm#UPGRD008

Leave a Comment

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

Twitter
YouTube
Pinterest
LinkedIn