What’s up internet?
I am Manish from RebelionRider.com. Recently we have completed tutorials on CASE and DECODE
functions I hope you enjoyed them! Today with this tutorial I am going to start another
series which will be on Data Pump in Oracle Database.
So Without further ado let’s start todays tutorial.
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.
As we know using data pump we can export as well as import the data to and from one database
to another database using expdp and impdp command line clients provided by data pump.
Using export (expdp) and Import (impdp) command line utilities we can either export or import
complete database or a subset of database for example database schema, tablespace or
even individual tables. So let’s start by learning how to export
complete database using expdp command line utility of data pump.
Exporting complete database or subset of database is a 3 step process.
Step 1: Make a Directory or Create a Folder The first step is to make a directory or in
windows language create a folder. Note here, this step must be performed by
privileged user such as sys or system and directory must be created on server system
rather than client system Just make a folder anywhere in your system
and give it some logical name. 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. So I am making a folder in my D drive by the
name of Data pump and inside the Data pump we are going to create another directory by
the name of full export. This folder full export will contain all the files created
by expdp utility of data pump. Before moving ahead let me tell you if you
are using Mac or LINUX systems then don’t worry steps for exporting data are the same.
Ok let’s move on to the 2nd step. Step 2: Making Directory Object and Granting
Privileges We just created a directory now in our second
step we will make a directory object and will grant this directory object some mandatory
privileges.This step should also be done by privileged user on server side thus I will
log on to my database using my sys user with sysdba privileges and for this I will use
command prompt. Let me quickly show you the user as you can see right now I am connected
to my database using my sys user. Now to create a directory object we use CREATE
DIRECTORY command. Let see how SQL>CREATE DIRECTORY orcl_full AS ‘D:Data
Pumpfull export’; Here in this query ORCL_FULL is a 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. Let’s hit enter directory created
You can share this directory object with the user who wants to perform the export. In our
case this user will be the HR user. 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. This is just a simple grant statement and
the grant succeeded SQL>GRANT read, write ON DIRECTORY orcl_full
TO hr; Now the plot is all set, but still HR user
cannot perform the full database export because HR user does not have required set of privileges.
Oracle has grouped these set of privileges into a role and that role is DATAPUMP_EXP_FULL_DATABASE.
So now we have to grant this role to the user HR.
By the way I have done a video on user roles where I have explained what the roles are
and how you can create a role as well as grant it to the users. If you want then you can
watch this video. Link is in the description box. Ok let’s grant this role to our user
HR SQL>GRANT DATAPUMP_EXP_FULL_DATABASE TO hr;
And grant succeeded Now everything is done. Let’s move on to our step 3. And the step
3 is exporting data Step 3: Export Data
To perform the export we use expdp command line utility of data pump and expdp is a command
prompt utility thus we cannot execute expdp on SQL prompt thus we have to come out of
our SQL prompt for that simply write exit. Now we are on our command prompt let’s clear
the screen first. Now we will write our expdp command. Let’s see how.
C:>expdp hr/[email protected] DIRECTORY=orcl_full DUMPFILE=orclfull.dmp LOGFILE=full_exp.log FULL=YES;
At the starting we have expdp which is a utility for unloading the data and metadata into a
set of operating system files called dump file sets. After this we have our user login
information using this information expdp utility of data pump will log into the database
And then we have DIRECTORY parameter which is set on the directory object orcl_full which
we just created. This parameter specifies the default location to which export can write
the set of dump file and log files. Followed by directory we have DUMPFILE parameter.
The dump file is made up of one or more disk files that contain table data, database object’s
metadata and control information. These files are written in binary format and dump files
can be imported only by data pump impdp import utility.
As these files are written in binary format by server using expdp export utility thus
they should not be tampered. Changing information in these file 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.
And then we have log file parameter 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. And at the end we have parameter FULL which
is set on YES indicates that all the data and metadata are to be exported.
At the back of the command prompt as you can see is our directory which we have specified
as our default location and as you can see it’s empty. Now let’s execute our expdp
command on command prompt for that simple hit enter time taken in exporting the data
depends on the size of your database. As you can see this process gonna take some
time so what I am going to do I am going to fast forward this process As you can see export is successful and let’s
take a look of our default directory and here is our dump and log file. Let’s check the
property of our dump file and as you can see here the size of our dump file is 169 MB and
here is our log file let’s take a look this log file also this log file contains all the
information of regarding this export if there is any error then I would have been listed
her in this log file. I suggest you to read my blog here on this
blog you’ll find lots of different things on data pump export such as different components
of data pump, working of data pump, introduction of expdp export utility, types of export in
data pump along with all the steps and commands of exporting full data base which I just showed
you with more in-depth explanation such as why directory object is required and restriction
with full database export. The link for this blog is in the description
Hope you enjoyed watching please hit the like button and share this video that really makes
me happy. Also please don’t forget to subscribe to my channel. Thanks for watching have a
nice day will see you soon till then take care I am Manish.