oracle 11g impdp returns invalid operation and object was not found

Question

I wanted to transfer an oracle table between two users using expdp and impdp commands.

First of all, I created a directory and granted proper privileges to source and destination users:

SQL> CREATE OR REPLACE DIRECTORY TEST_DIR AS '/u01/app/oracle/oradata/temp_dirs';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO SOURCE_USER;
Grant succeeded.
SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO DEST_USER;
Grant succeeded.
SQL> commit;

This is my expdp command and it's output:

oracle@lab-57:~$ expdp SOURCE_USER/SOURCE_USER tables=tbl_user directory=TEST_DIR dumpfile=users.dmp logfile=exp_users.log 

Export: Release 11.2.0.1.0 - Production on Tue Jul 24 16:18:27 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SOURCE_USER"."SYS_EXPORT_TABLE_01":  SOURCE_USER/******** tables=tbl_user directory=TEST_DIR dumpfile=users.dmp logfile=exp_users.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SOURCE_USER"."TBL_USER"                        8.085 KB       2 rows
Master table "SOURCE_USER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SOURCE_USER.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/oradata/temp_dirs/users.dmp
Job "SOURCE_USER"."SYS_EXPORT_TABLE_01" successfully completed at 16:18:38

This implies the export operation was done successfully.

Then I tried to import it using impdp as following:

oracle@lab-57:~$ impdp DEST_USER/DEST_USER tables=TBL_USER directory=TEST_DIR dumpfile=users.dmp logfile=imp_users.log

Import: Release 11.2.0.1.0 - Production on Tue Jul 24 17:26:58 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object DEST_USER.TBL_USER was not found.

I also tried tables=DEST_USER.TBL_USER and content=data_only; both returned same error.

Answer 1

As you're exporting as SOURCE_USER and importing as DEST_USER, the REMAP_SCHEMA parameter might help. (I'll split the command into several rows for easier reading)

impdp DEST_USER/DEST_USER tables=TBL_USER directory=TEST_DIR 
dumpfile=users.dmp logfile=imp_users.log
remap_schema=source_user:dest_user             --> this

If you're on 11.2, you might hit a bug which won't allow you to do that (see My Oracle Support for more info). In that case, you can still perform import, but you'll have to omit the TABLES parameter, i.e.

                          TABLES was in this gap
                                 |
impdp DEST_USER/DEST_USER        v       directory=TEST_DIR 
dumpfile=users.dmp logfile=imp_users.log
remap_schema=source_user:dest_user

Answer 2

Simple answer :

Create import : keep the dump ready.

Two option to restore the dump , consider complete dump or specific table.

For table specific :- Table name = MONTHS_DAY

impdp DBA_USER/DBA_Password tables = SOURCE_SCHEMA.MONTHS_DAY directory=AKSHAY_SPM_EDIR dumpfile=MONTHS_DAY.dmp logfile=impdpMONTHS_DAY.log remap_schema = SOURCE_SCHEMA:DESTINATION_SCHEMA

For complete import :

impdp DBA_USER/DBA_Password directory = AKSHAY_SPM_EDIR dumpfile=MONTHS_DAY.dmp logfile=impdpMONTHS_DAY.log remap_schema = SOURCE_SCHEMA:DESTINATION_SCHEMA

Make sure, directory is accessible as dump is present there