No tables found after running Expdp and Impdp in Oracle 12c

Question

I am trying to export a schema which only contains one table with one row on it. It has a table called "product" and resides in a tablespace called "tbs_data". Here's the expdp command I ran which completed successfully.

expdp dbauser/dbapasw@oradev schemas=myschema directory=my_data_pump_directory 
dumpfile=myschema_exp.dmp logfile=myschema_exp.log

When I ran the impdp, it complained about the "myschema" missing.

impdp dbauser/dbapasw@oradev schemas=myschema directory=my_data_pump_directory 
dumpfile=myschema_exp.dmp logfile=myschema_exp.log

So I created this user/schema manually and re-ran the impdp command. It completed successfully. However when I logged in as "myschema", I didn't see the product table. Also, from what I have been reading, the Impdp command was supposed to re-create the target user/schema when you run Impdp.

Could someone let me know what I am missing or how to resolve this?

Here's the actual log:

;;; 
Export: Release 12.1.0.2.0 - Production on Fri Jul 29 14:22:46 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "dbauser"."SYS_EXPORT_SCHEMA_01":  dbauser/********@oradev schemas=myschema directory=my_data_pump_directory dumpfile=myschema_exp.dmp logfile=myschema_exp.log 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "dbauser"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for dbauser.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\ADMIN\ORADEV\DPDUMP\myschema_EXP.DMP
Job "dbauser"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jul 29 14:22:54 2016 elapsed 0 00:00:07

Answer 1

I couldn't find what went wrong in your procedure and reproduce the same scenario either. I can help by demonstrating how to export one schema to another or in to the same database. I created the following example for this purpose.

Disclaimer: I don't have 12c instance to test it right now, but same procedure should work for 12c as well.

SQL> create user myschema identified by myschema;

User created.

SQL> grant connect, resource to myschema;

Grant succeeded.

SQL> conn myschema/myschema
Connected.
SQL> create table product(id number, name varchar2(20));

Table created.

SQL> insert into product values(1, 'sample_product_name');

1 row created.

SQL> commit;

Commit complete.

SQL> create directory dp_dir as '/home/oracle/Desktop';

Directory created.

SQL> exit 
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@nepal Desktop]$ clear

[oracle@nepal Desktop]$ expdp system/oracle schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp logfile=expdp_myschema.log

Export: Release 11.2.0.4.0 - Production on Sat Jul 30 07:02:07 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/password schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp logfile=expdp_myschema.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
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
. . exported "MYSCHEMA"."PRODUCT"                        5.429 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/Desktop/expdp_myschema.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jul 30 07:04:51 2016 elapsed 0 00:02:21

[oracle@nepal Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 30 07:10:06 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> drop user myschema cascade;

User dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


   [oracle@nepal Desktop]$ impdp system/oracle schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp

Import: Release 11.2.0.4.0 - Production on Sat Jul 30 07:11:44 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/password schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp 
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 "MYSCHEMA"."PRODUCT"                        5.429 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Sat Jul 30 07:12:14 2016 elapsed 0 00:00:19

[oracle@nepal Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 30 07:13:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> conn myschema/myschema
Connected.

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
PRODUCT

SQL> select * from product;

    ID NAME
---------- --------------------
     1 sample_product_name

SQL> 

Answer 2

First, make sure that the table has been actually exported into the dump file. You can do it by searching the following text line in the log file:

exported "..."."product" ... rows

If there is no such line in the log file, try to find an error message explaining why the export failed. Second, review the log file of impdp (I suggest you to use another name for it, for example ). It should contain the following text line:

imported "..."."product" X out of Y rows

Again, if there is no such text in the log file, try to find an error message explaining why the import failed.

If the problem persists, you may want to try OraDump-to-Oracle tool that automates import Oracle dump files to Oracle server.