[datapump] ORA-39034: Table TABLE_DATA: does not exist.
飞熊 • 2023年5月4日下午4:08 • 技术随笔 • 阅读 688

勤奋不是嘴上说说而已，而是实际的行动，在勤奋的苦度中持之以恒，永不退却。业精于勤，荒于嬉；行成于思，毁于随。在人生的仕途上，我们毫不迟疑地选择勤奋，她是几乎于世界上一切成就的催产婆。只要我们拥着勤奋去思考，拥着勤奋的手去耕耘，用抱勤奋的心去对待工作，浪迹红尘而坚韧不拔，那么，我们的生命就会绽放火花，让人生的时光更加的闪亮而精彩。

导读：本篇文章讲解 [datapump] ORA-39034: Table TABLE_DATA:

does not exist.，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

Keyword

问题

问题解决

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 Administration
Database 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