Mir Sayeed Hassan – Oracle Blog

How to import the Schema by using the impdp in Oracle Database 11gR2(11.2.0.4)

In the below scenario, I am going to perform the import of the schema “MIRTEST” from the dumpfile

Create a directory at OS Level

[oracle@testdb ~]$ mkdir -p /u01/dump/

Give the permisssion for directory & verify the Ownership

[oracle@testdb ~]$ chmod 775 /u01/dump/

[oracle@testdb_new u01]$ ll
drwxrwxr-x. 2 oracle oracle 6 Aug 9 20:05 dump

Create a directory at Database level & Give the appropriate permission

SQL> create directory dump as '/u01/dump';
Directory created.

SQL> grant read,write on directory dump to system;
Grant succeeded.

Transfer the Dumpfile to the /u01/dump location & start importing it

[oracle@testdb_new dump]$ ls
schema_mirtest.dmp schema_mirtest.log

Note: The Above dumpfile need to be restore in this database which is not exist

Verify the same schema exist in database or not

SQL> select username,default_tablespace from dba_users where username='MIRTEST';
no rows selected

As we would find the schema with a new of “MIRTEST”

Start import the schema “MIRTEST”

[oracle@testdb_new dump]$ impdp system directory=dump dumpfile=schema_mirtest.dmp logfile=imp_schema_mirtest.log

Import: Release 11.2.0.4.0 - Production on Sun Aug 9 20:21:06 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump dumpfile=schema_mirtest.dmp logfile=imp_schema_mirtest.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MIRTEST"."TEST1" 16.81 KB 107 rows
. . imported "MIRTEST"."TEST2" 7.007 KB 27 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Aug 9 20:21:14 2020 elapsed 0 00:00:03

Verify the Newly imported Schema “MIRTEST” & Table contain in it

Verify Users

SQL> select username,default_tablespace from dba_users where username='MIRTEST';

USERNAME     DEFAULT_TABLESPACE
----------------------------------
MIRTEST            USERS

Verify Tables

SQL> select table_name from dba_tables where owner='MIRTEST';

TABLE_NAME
----------
TEST1
TEST2

Note:

If the source schema dump contain the different tablespace name used, then you need to use the remap_tablespace option
If you would like to rename the schema name then you need to use the remap_schema option.

====Hence the schema got imported in database, tested & verified in our test env=====