I'm running in to a weird problem with IMPDP and Oracle DB version 11.2.0.4.0 where I have a table in the destination schema that does indeed exist, but IMPDP thinks it doesn't. See this entry from the import log file:
Starting "EXPORT_USER"."SYS_IMPORT_TABLE_01": export_user/******** directory=EXPORT_DIRECTORY dumpfile=SMARTCLOUDCONSOLE_OWNER_20170830.dmp TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS REMAP_SCHEMA=SMARTCLOUDCONSOLE_OWNER:MIKETESTCONSOLE_OWNER TABLES=SMARTCLOUDCONSOLE_OWNER.ACCOUNTDEPLOYMENTLINKS QUERY="WHERE account_id = (select account_id from accounts where lower(guid) = '#####')"
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" failed to load/unload and is being skipped due to error: ORA-00942: table or view does not exist
We are using VPDs such that the _owner
user owns all of the objects, so I don't think it's a problem of ownership. And it seems like it figures out that the table exists from the second line there Table "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" exists.
but then when it proceeds it fails with ORA-00942: table or view does not exist
What might be wrong with my use of IMPDP?
select account_id from MIKETESTCONSOLE_OWNER.accounts where...
)?export_user
I can runselect * from miketestconsole_owner.accounts;
and it returns me the data I'm expecting. But I can't run just aselect * from accounts;
, as that throws the same ORA-00942 error... I'll try prefacing the table with the schema in the QUERY clause again so I can get the error, but impdp didn't seem to like that any better. Any ideas for a workaround?