0

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?

Improve this question
6
  • i think it may be complaining about accounts table in your query.
    – Raj
    Aug 31, 2017 at 17:39
  • Just previously to running this impdp statement, I ran one to import the accounts table (needed different queries because the schema differs too much), and that works just fine... Do you think I need to preface that query with the destination schema (e.g. select account_id from MIKETESTCONSOLE_OWNER.accounts where...)?
    – Mike DeMille
    Aug 31, 2017 at 19:27
  • only if it is in a different schema, if it is in your own schema (to which you are importing into). OR see if the user running impdp has permissions.
    – Raj
    Aug 31, 2017 at 19:49
  • Turns out impdp doesn't like prefacing tables with the destination schema like that, and it didn't matter anyways because it's in the same schema. I'll have to see what I can dig up about the permissions.
    – Mike DeMille
    Sep 1, 2017 at 2:03
  • So if I connect to sqlplus as this export_user I can run select * from miketestconsole_owner.accounts; and it returns me the data I'm expecting. But I can't run just a select * 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?
    – Mike DeMille
    Sep 1, 2017 at 17:20

1 Answer 1

Reset to default
0

It looks like a permission issue. As Raj pointed out make sure that export_user has select privilege for the accounts table. Which it will have if it owns the table. Also make sure that it has select, insert, update and delete for MIKETESTCONSOLE_OWNER.ACCOUNTDEPLOYMENTLINKS and all of the tables in that schema. If tables are added over time, you probably need to add grants.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.