A schema is a collection of logical structures of data and database objects such as tables, indexes and synonyms etc. Oracle comes with some pre-installed sample schemas such as HR and SCOTTS. Furthermore schema has the same name as that of the user. Thus hereinafter I will refer schema as User.
So let’s see how you can import tables in different schema.
Importing tables in a different schema is very similar to importing tables in the same schema the only difference is that we have to resolve the ownership and tell the database about the new user of the table. In order to do so we use REMAP_SCHEMA parameter.
So let’s see how to import table in different schema and how to resolve the ownership by using REMAP_SCHEMA parameter of impdp data pump import.
In the above command I am importing a table superheroes which belongs to the user HR into a new user
MANISH whose login I have specified here (manish/manish@ORCL). I have already exported this table
superheroes into the directory
(exp_table is a directory object which is pointing to a directory
D:\Data Pump\Table Export) and the dumpfile created during the export of the superheroes table is
superehero.dmp which is the value of DUMPFILE parameter in the above command. Using parameter TABLES
in the above command I am specifying to the database that we want to import the table superhero.
But unlike the command of ‘importing table in same schema’ here we have an extra parameter which we have to specify when we import tables in a different schema which is REMAP_SCHEMA. This parameter takes two values – first is the old user of the table (HR) and second is the new user of the table (MANISH) both are separated by colons (:).
Although there are several significance of the remap schema parameter but here it’s especially resolving the ownership of the table which means this parameter is telling your database that the table superheroes is imported from user HR to user Manish thus adding the latter as its new owner and transferring all its data and metadata to it.
Step1. First you need to find the tablespace to which your table belongs. In my case I have to find the tablespace of the table Superheroes. To find the tablespace you can query tabs view provided by Oracle Database.
That’s all guys on this topic. Hope this article helped you in better understanding of the concept of importing tables in a different schema. Please do share it on your social networking and help me reach out to more people. Thanks & Have a great day!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.