Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.