oracle 11g impdp返回无效操作，并且找不到对象

Question

我想使用expdp和impdp命令在两个用户之间传输oracle表。

首先，我创建了一个目录，并授予源和目标用户适当的权限：

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;

这是我的expdp命令，它的输出：

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

这意味着导出操作已成功完成。

然后我尝试使用impdp导入它，如下所示：

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.

我还尝试了tables=DEST_USER.TBL_USER和content=data_only；两者都返回相同的错误。

Answer 1

导出为SOURCE_USER并导入为DEST_USER时，REMAP_SCHEMA参数可能会有所帮助。(为了便于阅读，我将把命令分成几行(

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

如果你使用的是11.2，你可能会遇到一个不允许你这样做的错误(请参阅我的Oracle支持以了解更多信息(。在这种情况下，您仍然可以执行导入，但必须省略TABLES参数，即

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

简单答案：

创建导入：准备好转储。

两种恢复转储的选项，考虑完整转储或特定的表。

对于特定于表的：-表名称=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

对于完全进口：

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

确保目录可访问，因为存在转储