Oracle

在 Oracle 12c 中執行 Expdp 和 Impdp 後未找到表

  • January 4, 2017

我正在嘗試導出一個模式，該模式僅包含一個表，其中一行。它有一個名為“product”的表並駐留在名為“tbs_data”的表空間中。這是我執行的 expdp 命令成功完成。

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

當我執行 impdp 時，它抱怨缺少“myschema”。

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

所以我手動創建了這個使用者/模式並重新執行了 impdp 命令。它成功地完成了。但是，當我以“myschema”身份登錄時，我沒有看到產品表。此外，根據我所閱讀的內容，Impdp 命令應該在您執行 Impdp 時重新創建目標使用者/模式。

有人可以讓我知道我缺少什麼或如何解決這個問題嗎？

這是實際的日誌：

;;; 
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

我也找不到您的程序中出了什麼問題並重現了相同的場景。我可以通過展示如何將一個架構導出到另一個架構或導出到同一個數據庫來提供幫助。為此，我創建了以下範例。

免責聲明：我現在沒有 12c 實例來測試它，但同樣的程序也適用於 12c。

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>

首先，確保表已實際導出到轉儲文件中。您可以通過在日誌文件中搜尋以下文本行來執行此操作：

導出的 “…”.“product” … 行

如果日誌文件中沒有這樣的行，請嘗試查找解釋導出失敗原因的錯誤消息。其次，查看 impdp 的日誌文件（我建議您使用其他名稱，例如 ）。它應包含以下文本行：

從 Y 行中導入“…”。“產品”X

同樣，如果日誌文件中沒有此類文本，請嘗試查找解釋導入失敗原因的錯誤消息。

如果問題仍然存在，您可能需要嘗試使用 OraDump-to-Oracle工具自動將 Oracle 轉儲文件導入 Oracle 伺服器。

引用自：https://dba.stackexchange.com/questions/145367

相關問答

Oracle

唯一約束錯誤 Oracle

  • August 6, 2015
檢視問答
Oracle

EXPDP - 為什麼塊估計顯著大於段大小？

  • October 28, 2021
檢視問答
Oracle

如何從 Oracle 導出中提取過程、函式等原始碼

  • September 24, 2016
檢視問答
Oracle

模式模式下的導出是否還包括表空間？

  • September 23, 2016
檢視問答
Oracle

Oracle 導出未正確導入（缺少一半以上的表）

  • September 21, 2016
檢視問答
Oracle

根據查詢導出到 INSERT 文本文件

  • August 3, 2016
檢視問答