Data Pump impdp - Table Mode and Rename Table(REMAP_TABLE).
The previous tutorial was the last mode of expdp data pump export where we learnt how to export tables of the schema / user. In today’s tutorial we will learn how to import tables and also how to create a duplicate copy of the table using impdp data pump import.
Data pump import is a utility for loading an export dumpfile set into a target system. You can invoke this utility using impdp command. The characteristics of the import operations are determined by the import parameters you specify. You can specify these parameters either on command line or in parameter file.
How to import table using impdp data pump import
The basic knowledge of data pump export is required to better understand the concept thus I would suggest you to have a look at my previous tutorials on Data Pump export.
Before performing the import you must know the directory object of the directory where dump-files are located also the names of dump-files from which you will import the tables.
Let’s see how you can import the table using impdp data pump import.
C:\> impdp hr/hr@ORCL DIRECTORY=demo
DUMPFILE=superhero.dmp LOGFILE=sh_imp.log TABLES=superheros;
Here in the above impdp command I am importing table superheros from dumpfile superhero.dmp. Let’s have an in-depth look at the above impdp command and its parameters.
Command starts with impdp which is a data pump utility for loading an export dumpfile set into a target system.
Followed by impdp command we have the login information of the user who wants to perform the import (In my case user is HR). Though specifying the SID (@ORCL) of your database is not mandatory yet if your system or server has multiple databases running in the background then it’s a good practice to avoid any conflict.
You need not be a sysdba to perform export or import however you require some system privileges.
Using directory parameter you can specify the default location in which the import job can find the dumpfile set and where it should create the log files.
The value of directory parameter is always a directory object. Directory object is a name mapped over a directory path. Or you can say that it’s a pointer pointing over a directory where impdp utility can find the dumpfiles created by expdp data pump export.
Using DUMPFILE parameter you can specify those dumpfiles which contains the tables which you want to import. If there are more than one dumpfiles then you can specify their names separated by commas.
Although LOGFILE parameter is optional yet it’s highly recommended to use it. Using this parameter you can generate and name a log-file which is written in human readable language and contain all the information regarding your import job. If there is an error using this file you can easily track them.
First using this parameter you specify that you are performing table-mode export. Second using this parameter you can specify the name of the tables which you want to import.
That’s how you import tables in your system using impdp data pump import.
REMAP_TABLE – How to duplicate/rename a table using impdp data pump import.
Suppose you want to import a table from your logical backup (table export) but when you checked your system you saw that some other user has created a table with the same name which you want to import.
Or there may be any other situation which requires you to make a duplicate copy of the table. Yes, I know there are other ways of making duplicate table in the system also but making a duplicate copy using impdp is a way as well.
REMAP_TABLE parameter of impdp data pump import let you rename the tables during an import operation.
Command of importing tables will remain the same as that of the previous command. We just have to add an additional parameter REMAP_TABLE. Let’s see how –
C :\> impdp hr/hr@ORCL DIRECTORY = demo DUMPFILE = superhero.dmp
LOGFILE = sh_imp.log TABLES = superheros
REMAP_TABLE = hr.superheros:superheros_copy;
REMAP_TABLE parameter takes two values first is the old name of the table along with the schema (hr.superheros) and second is the new name of the table which you want to give (superheros_copy). Both the names are separated using colons.
The execution of this command will give you the duplicate table. That’s it guys in this section on Table import. Hope it was helpful. Please share this on your social networking so that I could reach out to more people. Thanks & have a great day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
No Script used in this tutorial
I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7
You will get 5GB extra free cloud storage means total 20GB free cloud storage.