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.
C:\> impdp manish/manish@ORCL DIRECTORY=exp_table DUMPFILE=superhero.dmp LOGFILE= suphro_imp.log TABLES=’HR.SUPERHEROES’ REMAP_SCHEMA=HR: MANISH;
The execution of the above command will import the table superheroes which belongs to the user HR into the new user MANISH.
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(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.
REMAP_TABLESPACE
Sometimes it happens that the database of the target user has different tablespace. In such case the above command of importing tables can cause an error as the default impdp data pump import imports all the tables in the same tablespaces from which they were exported. In this case you can use REMAP_TABLESPACE parameter of impdp data pump import. Let’s see how:
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.
SELECT table_name,tablespace_name FROM tabs WHERE table_name=’SUPERHEROS’;
You need to login as the old user of the table to get the information. In my case the old user of table Superheroes is HR.
As you can see in the picture that the table superheroes reside into the Users tablespace.
Or you can query dba_tables view to find out the tablespace of your tables. For example
SELECT table_name, tablespace_name FROM dba_tables WHERE owner=’HR’;
The above query will show all the tables and their tablespaces under the ownership of HR.
Step2. Import the table using REMAP_SCHEMA
Now suppose you want to import the table superheroes into the tablespace Exp_tbs1 rather than the User Tablespace. For that first you need to come out of the SQL prompt and then execute the following command.
C:\>impdp manish/manish@ORCL DIRECTORY=exp_table DUMPFILE=superhero.dmp LOGFILE= suphro_imp.log TABLES=’HR.SUPERHEROES’ REMAP_TABLESPACE = USERS: EXP_TBS1;
Similar to REMAP_SCHEMA Parameter the REMAP_TABLESPACE also takes two values – first is the tablespace from which the table was originally exported (in my case it is USERS) and the second is the name of the tablespace in which you want to import the tables (EXP_TBS1).
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 network and help me reach out to more people. Thanks & Have a great day!
