One of the key reasons to export data is so that you can re-create database objects. You may want to do this as part of a backup strategy or to replicate data to a different database. Data Pump import uses an export dump file as its input and re-creates database objects contained in the export file.
The procedure for importing is similar to exporting:
1. Create a database directory object where you want to read/write Data Pump files.
2. Grant read and write privileges on the directory object.
3. From the OS prompt, run the impdp command.
Before running the import job, drop the INV table that was created previously. SQL> drop table inv purge;
Next, re-create the INV table from the export taken with the import:
$ impdp mv_maint/Pa$$w0rd123! Directory=dp_dir dumpfile=exp_inv.dmp logfile=imp_inv.log
You should now have the INV table re-created and populated with data as it was at the time of the export. Instead of dropping the table, you can also append the data to the table with the TABLE_EXISTS_ACTION parameter. Options are SKIP, APPEND, REPLACE, or TRUNCATE with APPEND being the default.
$ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=exp_inv.dmp table_ exists_action=append content=data_only
Use a Parameter File
Instead of typing commands on the command line, in many situations it is better to store the commands in a file and then reference the file when executing Data Pump export or import.
Using parameter files makes tasks more repeatable and less prone to error. You can place the commands in a file once and then reference that file multiple times.
Additionally, some Data Pump commands (such as FLASHBACK_TIME) require the use of quotation marks; in these situations, it is sometimes hard to predict how the OS will interpret them. Whenever a command requires quotation marks, it is highly preferable to use a parameter file.
To use a parameter file, first create an OS text file that contains the commands you want to use to control the behavior of your job.
This example uses the Linux vi command to create a text file named exp.par:
$ vi exp.par
Now, place the following commands in the exp.par file:
userid=mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=exp.dmp logfile=exp.log
tables=inv reuse_dumpfiles=y
Next, the export operation references the parameter file via the PARFILE command-line option:
$ expdp parfile=exp.par
Data Pump processes the parameters in the file as if they were typed on the command line. If you find yourself repeatedly typing the same commands or using commands that require quotation marks, or both, then consider using a parameter file to increase your efficiency.
Leave a Reply