Mir Sayeed Hassan – Oracle Blog

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

In this scenario, I am going to import at the table “test1” in schema “MIRTEST” from the given 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

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
table_test1.dmp table_test1.log

Note: The Above dumpfile which contain the table data, need to be restore in this database which is not exist in destination database

Verify the table “TEST1” exist in schema “MIRTEST” or not

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

TABLE_NAME
----------
TEST2

Note: Here the table “test1” does not exist, so need to restore from the dumpfile.

Start import the table “TEST1” in schema MIRTEST

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

Import: Release 11.2.0.4.0 - Production on Sun Aug 9 20:48:18 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=table_test1.dmp logfile=imp_table_test1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MIRTEST"."TEST1" 16.81 KB 107 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Aug 9 20:48:22 2020 elapsed 0 00:00:01

Verify the Newly imported table “TEST1” in schema “MIRTEST”

Verify Users

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

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

Note: TEST1 TABLE IS IMPORTED FROM THE GIVEN DUMPFILE

SQL> select count(*) from mirtest.test1;

COUNT(*)
-------
107

Note:

If the source schema & target schema is same,you can directly import the table into it
If the source schema & target schema is different then you need to use the remap_schema option
If the source schema tablespace & target schema tablespace are same then you can directly import without any issue.
If ther source schema tablespace & target schema tablespace is different then you need to use the remap_tablespace option

In my scenario the source & target schema, source & target schema tablespace was same, so there is no need to apply the remap option

=====Hence tested & Verified in our test env=====