All language subtitles for [SubtitleTools.com] Data Pump Export - Learning Oracle 12c [Video]

In this lesson, we're going
to look at data migration

using Data Pump Export.

So for years, Oracle DBA has
used an older tool in order

to export data.

And it was called
EXP for export.

And that's a command
line utility.

And it was used to pull
data out of a table

and dump it to what's
called a dump file.

And Oracle would store
that data in the dump file.

And then the dump file could
be transported somewhere else

and imported using
its pair tool, IMP.

Starting in 10g,
Oracle recommends

the use of Data Pump Export.

So Data Pump, both the export
and the import utilities

are referred to as Data
Pump, came out in 10g.

And when Oracle
released 10g, they

had some pretty big
boasts about Data Pump.

There were a lot of complaints
over the years about EXP

and IMP, and there was a
number of problems that

could occur when you ran them.

But mostly the complaint was
that they hadn't been updated

significantly in a long time.

Well, Data Pump
represented that update.

And so they are much different,
and yet similar tools,

but they can have a lot
of benefits with them.

And Oracle indicated
that Data Pump Export

can be up to 40 times faster
than the old export tool.

And from my own testing
and experiments,

I can say that that's
absolutely the case.

I mean, it is that much faster
in certain circumstances.

So both Data Pump
Export and Import

have a number of special
features to them.

So the first would
be compression.

So the output files
that are dumped out,

those dump files, can be
compressed on the fly,

to make them much
smaller and use up

significantly less space.

We also have encryption
available to us.

This is very
important for things

like when you're
dumping data out

of production to bring
into a development or test

environment, is that
encryption that's there.

We can export via network
link, actually a database link,

so we can actually export or
import data over a network.

So we save on the space that has
to be stored using those dump

files when we dump
those dump files out,

all the space that
must be used for that.

None of that is necessary
over the network,

as we pull the data directly.

We can use parallelism
with Export and Import.

And parallelism
allows us to have

a number of parallel
processes doing

the work at the same time.

And that's where quite a
bit of the performance gain

can be achieved, using
that parallelism.

We can export data
using a subquery.

So if we want to export
data from a table

but not all of the data,
we can use a subquery.

And then just dump
out part of the data.

And then we can use Data Pump
to rename tables or schemas,

or map them to new table spaces,
a number of different features

there as well.

So I'm here in the E App
Sreis Loader directory.

And what we want to
do is, we're going

to use Export to dump
data out into a dump file.

And so we're going to
start from scratch.

And when we do this,
we usually create

what's called a PAR
file, or Parameter file.

And it's going to have
a list of commands

that we want Data Pump
to use that directs

how we export our data.

I'm just going to create a text
file here, call it Deptexp.par.

And so let's put a number
of commands in here

that will direct data pump
export and how to export data.

First of all, we
give it a user ID.

We give it a directory object.

We give it a dump
file specifications.

This is what we want our
dump file to be named.

And this Percent U
is very important.

Because a Percent U is
just a keyword if you will

for allowing Data
Pump to give our dump

file a sequential number
in the name of it.

So if we were to export
and a number of files

were generated from the dump
file, then each one of them

would be sequentially
numbered so they're not

overwriting each other.

So it's a good practice to put
Percent U into your dump file

specification.

We'll specify a log file.

We'll give it a file size.

So this is the maximum
size of the dump file.

If it's any greater than that
then it'll create another one.

And since we have
Percent U, it'll

be numbered with the
next sequential number.

And then the table
that we want to export.

The tables equal Dept.

Save this.

And so we have our PAR file,
our Parameter file here.

And so in order to run
this, we'll use EXPDP.

So that's the command line
tool for Data Pump Export.

Our file, equal, Dept exp.par.

All right, so let's
look at our output here.

We invoke it.

Then it comes in here,
says starting Scott.

And what it does is, it
creates a system level table

with all the information
or the metadata

that it needs to process this.

And then it gives an estimation.

And then tells us here
that it successfully

completed, and the table, and
the number of rows that it did.

So it exports that
data out for us.

Now That's not the only type
of export that we can do.

Let's open this
up and modify this

to do a schema level export.

So we've done a table
level export here,

where we exported
the Dept table.

What if we wanted to export
all of the Scott schema?

Well, we can do that as well.

So we'll keep the
same user name and ID.

The directory object
will remain the same.

Here, for the log file, we'll
say Scott as the export.

And the dump file
will be Scott as well.

We're going to
make the file size

70K, kind of a demonstration of
how this Percent U will work.

And then schemas equal Scott.

And we'll Save and Close it.

And this time we'll
do the same command,

only we'll use the
Scott Par file.

Make a copy of that and
call it Scott EXP.par.

And execute.

So we notice that there's
quite a bit more output here,

because we've directed it
to export the entire schema.

And not only is that going
to include these tables,

but it's also going to include
any sequences, indexes,

constraints, those types
of things that we see.

Also notice that it wrote
multiple dump files, each one

given the sequential number.

And if we were to look out
on our loader directory,

we can see that those dump
files have been dumped out

as eight different files,
each with a sequential number.

And that keeps us from
overriding the same file

or reaching some maximum
size for the file.

So that's how we can use Data
Pump Export to export data.