Data Pump impdp – Schema Mode.

Performing the import of Schemas of your database is as simple as exporting them. Similar to expdp Schema export, we use SCHEMAS parameter to perform the import of the schema. SCHEMAS parameter specifies that user wants to perform Schema-Mode import. Also using Schema parameter helps you in choosing a particular schema from the multiple schema export to import. 

Like all the imports, Schema-Mode import also requires a dumpfile containing the required schema as a source along with a directory object pointing to the directory which is containing the Dumpfiles. Make sure these dumpfiles are created by expdp data pump export only because impdp Data pump import can only import the data from the dumpfiles created by expdp data pump export not from any other files. 

And to perform the Schema-Mode import either you need to be a privileged user such as sys/ system or you need to have Datapump_imp_full_database role.

I would highly suggest you to go through my tutorial number 54 on How to export Schema as the values of various parameters such as Directory and Dumpfiles are used from that tutorial.

There could be two situations related to the import of the schema:

  1. The target database does not contain the schema by the name of the one which you are going to import.
  2. The target database already has a schema by the name of the one which you are going to import.

The first scenario is the simplest one which does not require you to do any hard work. In this kind of situation you can directly perform the import of the schema. 

But the second situation is quite tricky. As you already have a schema with the same name of the one which you are going to import thus first you have to decide whether there is still requirement of the already existing schema or not. If not then you can drop that schema. But in real scenarios dropping a whole schema might lead you in a bigger trouble so when you cannot drop the schema then there is only one choice left which is to make a duplicate copy of the schema which already exists. For making a duplicate copy of the schema you can tell your impdp data pump import utility to rename the schema first and then import it into the database. 

Let’s see the impdp commands for both the situations.

The impdp data pump import command for situation 1 when database does not has a schema by the same name of the one which you are going to import is –

C:\>impdp  system/oracle@ORCL  DIRECTORY = exp_schema  DUMPFILE = exp_schm.dmp  SCHEMAS = scott

Executing this impdp data pump import command will import the schema Scott into your database. 
But in Situation 2 when target database already has a schema with same name then this impdp command will return an ORA-31684 error stating that “Object Type USER:”SCOTT” already exists”

In this case, as I mentioned above, you can either drop the schema with the same name from your database or make a duplicate copy of the schema. To make a duplicate copy you can use REMAP_SCHEMA parameter of the impdp data pump import command. Let’s see how

C:\>impdp system/oracle@ORCL  DIRECTORY = exp_schema  DUMPFILE = exp_schm.dmp  SCHEMAS = scott  REMAP_SCHEMA = SCOTT : SCOTT_COPY

Remap_schema parameter takes two values. First is the real name of the schema which we are importing which is Scott in our case and second one is the new name of the schema. As new name you can give whatever name you want. Here I have designated Scott_Copy as the new name.

So that’s all on this topic. Hope you found this article useful. Kindly do share this on your social network and help me reach out to more people. Thanks & have a great day!

1 COMMENT