Data Pump Schema Mode.
A schema is a collection of a logical structure of data or, database objects owned by a database user and shares the same name as the user.
Using expdp export utility you can export any schema of your database or we can also say that using expdp export data pump we can take logical backup of any schema in Oracle database. In this tutorial you will learn how to export schema using expdp data pump in Oracle Database.
Step1: Create a Directory.
Create a directory anywhere in your system and name it whatever you want. Make sure that the directory must be created on the server and not on the client system and Creation of directory must be performed by a DBA. This directory will serve as the default location for storing all the exported files such as dump files and log files.
For this tutorial I’ll use a directory Schema Export and the location of this directory is
D:\ Data Pump\ Schemas Export
Step 2: Create Directory Object and grant it mandatory privilege.
2.1. Log on to database as sys user
Creating a directory object and granting privileges must be performed by DBA thus I would recommend you to log on to your database as sys user with sysdba privileges or ask your DBA to do this work for you.
C:\> sqlplus / as sysdba
2.2. Create Directory Object
Directory object is a pointer pointing over a directory which you want your expdp utility to use when storing all exported files.
To create a directory object you can use CREATE DIRECTORY command. Mind here that create directory will not create any physical directory in your system.
SQL>CREATE DIRECTORY exp_schema AS ‘D:\Data Pump\Schema Export’;
In this query we created a directory object and named it exp_schema which is pointing to the directory Schema Export, which we created in the first step.
2.3. Grant Read and Write Privileges on The Directory
After creating a directory we need to grant the mandatory privileges over this directory to the user who wants to perform the export. Say user HR wants to perform the schema export thus we will grant the mandatory privilege such as read and write over the directory Schema Export by using directory object exp_schema which we just created.
SQL>GRANT read, write ON DIRECTORY exp_schema TO hr;
Suggested Reading: How to grant System Privilege.
2.4: Grant DATAPUMP_EXP_FULL_DATABASE role
Apart from granting read and write privilege on the directory to the user we also need to grant DATAPUMP_EXP_FULL_DATABASE role to the user who wants to perform the export.
Why should we grant DATAPUMP_EXP_FULL_DATABASE role to the user?
Because DATAPUMP_EXP_FULL_DATABASE role allows you to
- Specify a single schema other than your own or a list of schema names.
- Export additional non-schema object information for each specified schema so that the schemas can be re-created at import time. This additional information includes the user definitions themselves and all associated system and role grants, user password history, and so on.
This means that the users which don’t have this role can only export own schema. For example if our user HR doesn’t have this role then it can only export its own schema which is HR. If user HR wants to export other schemas than its own such as OE, SH or Scott then HR needs to have this role.
Let’s grant this role to user HR
SQL>GRANT DATAPUMP_EXP_FULL_DATABASE TO hr;
Step 3: Export the Schema
Now everything is set and user HR can perform the export using expdp data pump utility.
As I have told in my previous tutorial also that expdp is a command line utility thus we can only execute it on our command prompt. Thus if you are on your SQL prompt then first you need to exit it.
Let’s perform the export.
C:\> expdp hr/hr@ORCL DIRECTORY = exp_schema DUMPFILE =exp_schm_scott.dmp LOGFILE=scott_lg.log
SCHEMAS = scott
The last parameter of this query which is “SCHEMA” tells the data pump that we want to perform a schema export which means that we want to run data pump expdp utility in schema mode. Secondly using this schema parameter you specify the names of schema which you want to export. For example here I have specified SCOTT schema.
Similarly you can export multiple schemas in single expdp command for that you just need to specify the names of all the schemas which you want to export separated by commas.
C:\> expdp hr/hr@ORCL DIRECTORY = exp_schema DUMPFILE =exp_schm_hr_oe.dmp LOGFILE=hr_oe_lg.log
SCHEMAS = hr, oe
Here in this query we are exporting two schemas which are HR and OE. If you will look closely at the values of SCHEMA parameter, they are set on HR and OE.
So that’s all on Schema export. I hope you found it helpful. Kindly please share it on your social network and help me reach out to more people. Also, you can connect with me on my Facebook page. Thanks & have a great day!