export full database using expdp by Manish Sharma

Full export using expdp export utility in Data Pump.

Data pump in Oracle database is a server-based technology which enables very high-speed movement of data and metadata from one database to another.

Component of Data pump in Oracle Database

Data pump is made up of 3 components

  1. The Command-line clients, expdp and impdp
  2. The DBMS_DATAPUMP PL/SQL package ( a.k.a. Data Pump API )
  3. The DBMS_METADATAPL/SQL package ( a.k.a. metadata API )

 

How Data pump works (Working of Data pump)

The command-line client expdp and impdp use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands using the parameters entered at the command line. Furthermore, these parameters enable the exporting and importing of data and metadata for a complete database or for subsets of a database. 

Similarly, when metadata is moved, Data pump uses functionality provided by the DBMS_METADATA PL/SQL package. This package provides a centralized facility for the extraction, manipulation, and recreation of dictionary metadata.

The DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages can be used independently of the Data Pump clients.

 

Exporting Data using Data Pump in Oracle Database (expdp Intro)

Using expdp utility provided by data pump we can export data or say unload data or metadata from one database to another. Therefore, using this utility you can either export the complete database or a subset of databases such as Database Schema, Tablespaces or even individual tables. 

Types of Export in Data Pump

There are 4 types of export which you can perform using export (expdp) utility provided by Data pump in Oracle Database. These exports are as follows:

  1. Full Export
  2. Schema Export
  3. Table Export
  4. Tablespace Export

 

Steps for performing Full Database export

Step1. Make a Directory or Create a Folder

Note here that this step must be performed by the privileged user such as sys or system. In addition to that, the directory must be created on server system rather than the client system.

Just make a folder anywhere in your system and give it some logical name. Furthermore, if this export is a part of your backup strategy then it’s advisable to avoid making the folder on the same partition which also contains your Oracle home directory or OS bootable files.

For the demonstration I have created a directory by the name of full export inside the directory Data pump in D drive. This full export folder will contain all the files created by expdp utility of data pump.

Directory Path
D:\Data Pump\Full Export

Step 2: Making Directory Object and Granting Privileges

This step 2 is divided into few sub steps which you have to follow. Furthermore it should be done by privileged users such as sys on server side.

2.1. Log on to database as sys user

For making a directory object, log onto your database as sys user with sysdba privileges

 C:\> sqlplus / as sysdba

2.2. Create Directory Object

To create a directory object we use CREATE DIRECTORY command. 

SQL> CREATE DIRECTORY orcl_full AS ‘D:\Data Pump\full export’;

Here in this query ORCL_FULL is a directory object (you can give whatever name you want to your directory object) which is just a name mapped over a directory path. Or you can say that it’s just a pointer pointing over a directory which you want your expdp utility to use when storing all exported files. Mind here CREATE DIRECTORY command will not create any actual directory in your system this command only helps you in creating a directory object.

Why Directory Object is required?

A directory object is required to ensure data security and integrity.
For example:

1. If you were allowed to specify a directory path location for an input file, then you might be able to read data that the server has access to, but you do not.
2. If you were allowed to specify a directory path location for an output file, then the server might overwrite a file that you might not normally have privileges to delete.

You can share this directory object with the user who wants to perform the export. In my case this user will be the HR user.

2.3. Grant Read and Write Privilege on The Directory

The next thing after creating a directory object is to grant read and write privileges on the directory to the user who wants to perform the export of data. Let’s see how 

SQL> GRANT read, write ON DIRECTORY orcl_full TO hr;

2.4. Grant mandatory Role to the user

To perform a full export, you must have the DATAPUMP_EXP_FULL_DATABASE role. Thus we need to grant this role to our user HR.

SQL> GRANT DATAPUMP_EXP_FULL_DATABASE TO hr;

Step 3: Export the Data

Since we have done all the required settings, we are good to go.

C:\>expdp   hr/hr@ORCL   DIRECTORY=orcl_full   DUMPFILE=orclfull.dmp   LOGFILE=full_exp.log   FULL=YES;

Expdp is a utility for unloading the data and metadata into a set of operating system files called dump file sets. 

 

Parameters used in the above EXPDP command.

User Login: 

The first parameter is a user login. Here you provide the login information of that user using which you want to perform the export. So here in my case I have provided the login of my hr user (Hr/Hr) accompanied with the SID of my database which is ORCL.

DIRECTORY: 

Using DIRECTORY parameter you specify the default location to which Export can write the dump file set and the log file. Here in my case DIRECTORY parameter is set on the directory object orcl_full which we just created. 

DUMPFILE: 

Using DUMPFILE parameter you specify the names, and optionally, the directory objects of dump files for an export job. 

The dump file is made up of one or more disk files that contain table data, database object’s metadata and control information. In addition to that, these files are written in binary format and dump files can be imported only by data pump impdp import utility. As they are written in binary format by server using expdp export utility thus they should not be tampered. Changing information in these files might compromise the data consistency which may cause an error while importing. 

You can supply multiple file names here separated by comma. Dot dmp is the default extension for dump file. If no extension is given then expdp export utility will use this default extension. Though you can specify whatever extension you want but it’s always advisable to use the oracle recommended extensions.

Log file: 

Using log file parameter expdp export utility will generate a human readable log file for you which are very helpful in tracking the status of your export.

FULL: 

This parameter full indicates that you want to perform a full database export. This parameter can have YES or NO values. If you set this parameter to YES that means expdp utility will export all the data and metadata of the database.

If you are like me who learns better by watching Videos then here is the Video Tutorial for you.

Restrictions with Full Database export.

  • A full export does not export system schemas that contain Oracle-managed data and metadata. Examples of system schemas that are not exported include SYS, ORDSYS, and MDSYS.
  • Grants on objects owned by the SYS schema are never exported.
  • Finally, If you are exporting data that is protected by a realm, then you must have authorization for that realm.

That’s it friends. Hope this information was helpful. Kindly please share this on your social network & help me reach more people. Also you can connect with me on my Facebook Page for interesting SQL trivia. Thanks & have a wonderful day!