[datapump] ORA-39034: Table TABLE_DATA:<table Name> does not exist.

Keyword:

ORA-39034: Table TABLE_DATA:

does not exist.

问题：

为了调查问题，在导入用户提供的ASH Dump时，发生了ORA-39034错误，
无法正常导入数据。

  SQL> create user teacherWhat identified by teacherWhat;    User created.    SQL> grant dba to teacherWhat;    Grant succeeded.    SQL> conn teacherWhat/teacherWhat    Connected.    SQL> create or replace directory test_dir as '/temp/temp';    Directory created.    SQL> !impdp teacherWhat/teacherWhat directory=test_dir dumpfile=exp_hist_active_sess_history.dmp tables=EXP_HIST_ACTIVE_SESS_HISTORY    Import: Release 12.1.0.2.0 - Production on Tue Nov 7 02:38:13 2017    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    Master table "teacherWhat"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded    import done in UTF8 character set and AL16UTF16 NCHAR character set    export done in AL32UTF8 character set and AL16UTF16 NCHAR character set    WARNING: possible data loss in character set conversions    Starting "teacherWhat"."SYS_IMPORT_TABLE_01":  teacherWhat/******** directory=test_dir dumpfile=exp_hist_active_sess_history.dmp tables=EXP_HIST_ACTIVE_SESS_HISTORY    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    ORA-39034: Table TABLE_DATA:"teacherWhat"."EXP_HIST_ACTIVE_SESS_HISTORY" does not exist. ★    Job "teacherWhat"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Nov 7 02:38:29 2017 elapsed 0 00:00:07

问题解决：

1.查看在线文档，看看错误的含义。

Home / Database / Oracle Database Online Documentation 12c Release 1 (12.1) / Database AdministrationDatabase Error Messages

https://docs.oracle.com/database/121/ERRMG/ORA-38029.htm#ERRMG-GUID-1B677A70-6415-4030-9B89-9518DBBF2E07

>ORA-39034: Table string does not exist.>Cause: The user referenced a table in an API that did not exist.>Action: Correct table name and retry API.

貌似没有什么意义，不知如何解决。

尝试1：
也许是导入用户的问题，尝试用system用户导入，依然无果.

    SQL> !impdp system/oracle directory=test_dir dumpfile=exp_hist_active_sess_history.dmp tables=EXP_HIST_ACTIVE_SESS_HISTORY    Import: Release 12.1.0.2.0 - Production on Tue Nov 7 02:42:10 2017    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    ORA-39002: invalid operation    ORA-39166: Object SYSTEM.EXP_HIST_ACTIVE_SESS_HISTORY was not found or could not be exported or imported.

看来表就是teacherWhat用户的,不过表已经存在了。
也许与用户和表有关系

尝试2：
删除用户，再导入：

   SQL> conn / as sysdba    Connected.    SQL> drop user teacherWhat cascade;    User dropped.    SQL> !impdp system/oracle directory=test_dir dumpfile=exp_hist_active_sess_history.dmp tables=teacherWhat.EXP_HIST_ACTIVE_SESS_HISTORY    Import: Release 12.1.0.2.0 - Production on Tue Nov 7 02:52:29 2017    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    Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded    import done in UTF8 character set and AL16UTF16 NCHAR character set    export done in AL32UTF8 character set and AL16UTF16 NCHAR character set    WARNING: possible data loss in character set conversions    Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=test_dir dumpfile=exp_hist_active_sess_history.dmp tables=teacherWhat.EXP_HIST_ACTIVE_SESS_HISTORY    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    ORA-39034: Table TABLE_DATA:"teacherWhat"."EXP_HIST_ACTIVE_SESS_HISTORY" does not exist.    Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Nov 7 02:52:38 2017 elapsed 0 00:00:05

依然错误。

尝试3：
建立一个空表，再导入。

    SQL> create user teacherWhat identified by teacherWhat;    grant dba to teacherWhat;    conn teacherWhat/teacherWhat    User created.    SQL>    Grant succeeded.    SQL> SQL>    Connected.    SQL>    SQL>    SQL> create table teacherWhat.EXP_HIST_ACTIVE_SESS_HISTORY as select * from DBA_HIST_ACTIVE_SESS_HISTORY where 1=0;    Table created.    SQL> !impdp teacherWhat/teacherWhat directory=test_dir dumpfile=exp_hist_active_sess_history.dmp tables=teacherWhat.EXP_HIST_ACTIVE_SESS_HISTORY    Import: Release 12.1.0.2.0 - Production on Tue Nov 7 02:55:44 2017    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    Master table "teacherWhat"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded    import done in UTF8 character set and AL16UTF16 NCHAR character set    export done in AL32UTF8 character set and AL16UTF16 NCHAR character set    WARNING: possible data loss in character set conversions    Starting "teacherWhat"."SYS_IMPORT_TABLE_01":  teacherWhat/******** directory=test_dir dumpfile=exp_hist_active_sess_history.dmp tables=teacherWhat.EXP_HIST_ACTIVE_SESS_HISTORY    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    . . imported "teacherWhat"."EXP_HIST_ACTIVE_SESS_HISTORY"    1.659 MB    3611 rows    Job "teacherWhat"."SYS_IMPORT_TABLE_01" successfully completed at Tue Nov 7 02:55:58 2017 elapsed 0 00:00:11

终于成功了。
猜想原因，也许dump中并没有该表的定义。也许用户是通过DATA_ONLY导出的数据。

版权声明：本文为博主原创文章，转载必须注明出处，本人保留一切相关权力！http://blog.csdn.net/lukeunique

欢迎关注微信订阅号：TeacherWhat