ORA-31685 during a DataPump import: three different causes, same error text

The error

ORA-31685: Object type USER:"APPUSER" failed due to insufficient privileges

Of all the errors that show up in a DataPump import log, ORA-31685 is the one worth understanding carefully — the object it's attached to was not created at all, unlike most DataPump errors which mean "already exists" or "created with warnings." But across real migrations, this exact error text has turned out to mean three genuinely different things, depending only on what object type it's attached to. Generalizing from one past migration to the next is exactly how this one catches people out.

Case A — missing a system privilege the DDL type requires

ORA-31685: Object type TRIGGER:"<SCHEMA>"."<TRIGGER_NAME>" failed due to insufficient privileges

What it means: creating a DATABASE TRIGGER (one that fires on system-level events like LOGON or STARTUP, not a table-level trigger) requires the ADMINISTER DATABASE TRIGGER system privilege on the owning schema. A freshly-built target database doesn't grant this by default just because the schema/user was created.

Real case: a migration carried over a schema with two database triggers — one firing on LOGON, one on instance STARTUP (populating a KEEP buffer pool). Both failed to import with this error on the first pass, because the privilege check happens once per schema, not per trigger — so a single missing grant blocks every database trigger that schema owns.

Fix — grant the privilege once per affected schema, ideally before the import runs:

GRANT ADMINISTER DATABASE TRIGGER TO <schema>;

Then re-run just the trigger objects from the dump file, or let a subsequent retry pass pick them up.

Case B — missing an object privilege for cross-schema DDL

ORA-31685: Object type INDEX:"<SCHEMA_A>"."<INDEX_NAME>" failed due to insufficient privileges

What it means: <SCHEMA_A> is trying to create an index on a table that belongs to a different schema (<SCHEMA_B>). Doing that requires CREATE ANY INDEX — a privilege that has nothing to do with <SCHEMA_A>'s own objects and everything to do with reaching into someone else's schema.

Real case: a reporting schema had, over years of operation on the source database, accumulated dozens of indexes on another schema's core tables — a common pattern where a reporting layer indexes the tables it queries most, without owning them. On the freshly-built target, the reporting schema had never been granted CREATE ANY INDEX, so those cross-schema indexes failed on the same privilege check.

Fix:

GRANT CREATE ANY INDEX TO <schema_a>;

Then either let DataPump retry those specific objects, or extract their exact DDL from the existing dump file without touching the source database:

impdp system/<pwd>@<target> directory=mig_export dumpfile=full_%U.dmp \
  sqlfile=idx_recover.sql include=INDEX:"IN ('IDX1','IDX2',...)"

The generated .sql file contains the complete, verbatim CREATE INDEX statements captured at export time — useful when the source database is no longer available to regenerate DDL from.

Case C — a broken dependency reported as a privilege failure

ORA-31685: Object type REFRESH_GROUP:"<SCHEMA>"."<GROUP_NAME>" failed due to insufficient privileges.
ORA-31685: Object type MATERIALIZED_VIEW:"<SCHEMA>"."<MV_NAME>" failed due to insufficient privileges.
  (query references a remote database link)

What it means: the materialized view's defining query (or the refresh group coordinating it) references a database link that doesn't exist on the target. Oracle reports this as an "insufficient privileges" failure even though no grant would fix it — the real problem is a missing dependency, not a missing permission.

Real case: an archive/reporting target didn't recreate the database links the source used to pull data from remote systems, since the archive doesn't need live refresh. Every materialized view and refresh group built on those links failed with ORA-31685. Critically, this turned out not to matter: the physical tables backing those materialized views — the actual data — had imported successfully as ordinary tables, independent of the MV definition. A row-count check against the source confirmed the data matched:

SELECT COUNT(*) FROM <schema>.<underlying_table>;

What to do: don't reach for a grant here — it won't help. Instead:

1. Confirm whether the underlying data table imported correctly (it usually did — the base table and the MV definition are imported as separate objects).
2. Decide whether the missing DB link is actually needed. If the target doesn't need live refresh (e.g. an archive or reporting copy), the MV/refresh-group failure is inconsequential.
3. If refresh is needed later, recreate the DB link first, then regenerate the MV/refresh-group DDL from the dump file (sqlfile= as in Case B) and run it once the link exists.

Why the same error needs three different responses

The three cases share the same error text but call for completely different responses:

• Case A (trigger): real problem is a missing system privilege; data is not affected (trigger logic only); fix: GRANT ADMINISTER DATABASE TRIGGER
• Case B (cross-schema index): real problem is a missing object privilege; data is not affected (index is derived, rebuildable); fix: GRANT CREATE ANY INDEX
• Case C (MV/refresh group): real problem is a missing dependency (DB link); data is not affected (underlying table data imports separately); fix: investigate the dependency — no grant will fix this

The wrong response in each case: applying whatever fixed it on the last migration. Case A's fix does nothing for Case B's privilege level. Case B's fix does nothing for Case C — which isn't a privilege problem at all.

The takeaway