In this tutorial, you will learn how to import and export Oracle Database schemas with Data Pump utility. Oracle Data Pump introduced with Oracle 10g. Its features include significant structural and functional enhancements over the initial import and export utilities. Data Pump runs as a server process, benefiting users in multiple ways. The following are the steps to perform Oracle Database export and import using Data Pump.
Create a Directory Object in Oracle
Oracle Data Pump requires a directory to store data files and log files. Use the CREATE DIRECTORY command to create a directory object in Oracle which refers to an external directory on the disk. Users who will access the Data Pump files must have the READ and WRITE privileges on the directory. Also, the user who is creating the directory in Oracle should have CREATE ANY DIRECTORY privilege. Below is an example:
CREATE OR REPLACE DIRECTORY dbpump AS 'f:\test\dbpump';
Folder f:\test\dbpump must exists. Now grant privileges to the user who will perform export and import. The following is an example:
GRANT READ, WRITE ON DIRECTORY DBPUMP TO VINISH;
The user VINISH now can use the DBPUMP directory for Data Pump jobs.
Some Important Data Pump Export Options
Oracle provides the utility expdp that serves as the interface to Data Pump. Below I am describing some essential parameters to use with expdp command, for more details on the parameters, you can check the following link Oracle Data Pump.
EXPDP Parameters
| Parameter | Description |
|---|---|
| CONTENT | What is to be exported: DATA_ONLY, METADATA_ONLY, or ALL. |
| DIRECTORY | Specifies the Oracle Database directory object. |
| DUMPFILE | Specifies the DUMP file name. |
| PARFILE | Parameter file to use. |
| SCHEMAS | Name of the schemas to be exported. |
Exporting With Data Pump in Oracle Example
In the following Data Pump export, the user VINISH will export the SCOTT schema's metadata (Procedures, functions, table and view structures) only.
expdp vinish/vinpsw@orcl dumpfile=scott.dmp content=metadata_only directory=dbpump schemas=scott
Output
Export: Release 11.2.0.1.0 - Production on Fri Mar 29 14:26:45 2019 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 "VINISH"."SYS_EXPORT_SCHEMA_01": vinish/********@orcl dumpfile=scott.dmp content=metadata_only directory=dbpump schemas=scott 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE 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/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS 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 Master table "VINISH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for VINISH.SYS_EXPORT_SCHEMA_01 is: F:\TEST\DBPUMP\SCOTT.DMP Job "VINISH"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:27:03
You can perform the above export using the parameter file also. Here is an example:
Create a parameter file to any directory, for example, f:\test\dbpar.par as following:
DIRECTORY=DBPUMP CONTENT=METADATA_ONLY DUMPFILE=SCOTT.DMP SCHEMAS=SCOTT
Save the changes to the file. Now run the expdp utility using this parameter file. Below is an example:
expdp vinish/vinish@orcl parfile=f:\temp\dbpar.par
Importing With Data Pump in Oracle Example
The impdp utility is used to import data with Data Pump in Oracle. Same as expdp, the directory object also needed to perform import with impdp, you can create a new directory object in Oracle to refer external directory, or you can use the same directory.
Some IMPDP Parameters
| Parameter | Description |
|---|---|
| CONTENT | What is to be exported: DATA_ONLY, METADATA_ONLY, or ALL. |
| DIRECTORY | Specifies the Oracle Database directory object. |
| DUMPFILE | Specifies the DUMP file name. |
| PARFILE | Parameter file to use. |
| SCHEMAS | Name of the schemas to be exported. |
Import Example
In the following Data Pump import, we will import the dump file; we exported above.
impdp vinish/vinish@orcl dumpfile=scott.dmp directory=dbpump schemas=scott
Import with parameter file (f:\test\dbpar.par) example:
DIRECTORY=DBPUMP CONTENT=DATA_ONLY DUMPFILE=SCOTT_ALL.DMP SCHEMAS=SCOTT
impdp vinish/vinish@orcl parfile=f:\temp\dbpar.par
