When Oracle Data Pump Remembers an Old View Definition

A short troubleshooting note for an Oracle Data Pump VIEWS_AS_TABLES failure where expdp kept reporting a column that no longer existed in the current view definition.

A short troubleshooting note for an Oracle Data Pump VIEWS_AS_TABLES failure where expdp kept reporting a column that no longer existed in the current view definition.

While preparing an initial load with Oracle Data Pump, I ran into an error that looked straightforward at first:

DATAPUMP

Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

ORA-31693: Table data object "ELHUB_SECURITY_MAPPING"."YOGI_EXP_IDENTITY"
failed to load/unload and is being skipped due to error:

ORA-00904: "UPDATE_AT": invalid identifier

The obvious explanation was that the exported view still referenced UPDATE_AT.

It did not.

The view had already been changed to use:

SQL

CREATED_AT
UPDATED_AT

The view compiled, querying it directly worked, and recreating it did not change the Data Pump error.

Yet expdp continued reporting the old UPDATE_AT column.

That was the useful clue.

The problem was no longer the current view

When an error references metadata that no longer exists in the current database object, repeatedly changing the source SQL can send the investigation in the wrong direction.

In this case the export used:

CONFIG

VIEWS_AS_TABLES

A previous failed export had left Data Pump-related state behind. After tracing the job and checking temporary/internal objects, I found stale KU$VAT_* objects associated with the failed VIEWS_AS_TABLES execution.

Once those stale objects were removed safely, the next export used the current view definition and completed successfully.

1. Verify the current view first

Before blaming Data Pump state, verify that the source object is actually correct.

SQL

SELECT *
FROM ELHUB_SECURITY_MAPPING.YOGI_EXP_IDENTITY
WHERE 1 = 0;

Check the column metadata as well:

SQL

SELECT column_name
FROM dba_tab_columns
WHERE owner = 'ELHUB_SECURITY_MAPPING'
  AND table_name = 'YOGI_EXP_IDENTITY'
ORDER BY column_id;

In my case the current definition contained UPDATED_AT, not UPDATE_AT.

If Data Pump still reports the old name, continue with the job state rather than rewriting the view again.

2. Check Data Pump jobs

Check whether a Data Pump job is still active or attached before touching any internal objects.

SQL

SELECT owner_name,
       job_name,
       operation,
       job_mode,
       state,
       attached_sessions,
       datapump_sessions
FROM dba_datapump_jobs
ORDER BY owner_name, job_name;

The important rule here is simple:

Do not remove internal Data Pump objects while the corresponding job is still active.

If deeper diagnostics are needed, attach to the job with DBMS_DATAPUMP.ATTACH and inspect its status with DBMS_DATAPUMP.GET_STATUS. This is often more useful than looking only at the final expdp log because it exposes the current job state and Data Pump error/WIP messages.

3. Look for stale KU$VAT_* objects

After confirming that no active job is using them, inspect objects left by the failed operation.

SQL

SELECT owner,
       object_name,
       object_type,
       status,
       created,
       last_ddl_time
FROM dba_objects
WHERE object_name LIKE 'KU$VAT%'
ORDER BY owner, object_name;

I also used table comments as another way to identify them:

SQL

SELECT owner,
       table_name,
       comments
FROM dba_tab_comments
WHERE table_name LIKE 'KU$VAT%'
ORDER BY owner, table_name;

In this incident, stale KU$VAT_* objects remained after a failed VIEWS_AS_TABLES export and still reflected the earlier structure.

That explained the apparently impossible ORA-00904.

4. Remove only confirmed stale objects

Once the related Data Pump job is confirmed inactive and the object is known to belong to the failed attempt, remove the stale object.

CLEANUP

DROP TABLE "<OWNER>"."<KU$VAT_OBJECT>" PURGE;

I also removed the dump and log files from the failed run before retrying the export.

Do not turn this into a generic cleanup rule for every KU$VAT_* object. Treat these as Oracle-internal objects and remove them only after correlating them with the failed job.

5. Run expdp again

After cleanup, rerun the export normally.

FLOW

verify current view
        ↓
check DBA_DATAPUMP_JOBS
        ↓
trace / inspect Data Pump job state
        ↓
identify stale KU$VAT_* objects
        ↓
confirm no active job owns them
        ↓
remove only confirmed stale objects
        ↓
remove failed dump/log artifacts
        ↓
run expdp again

The next execution picked up the current view definition with CREATED_AT and UPDATED_AT and completed successfully.

Why this is worth remembering

The interesting part of this case was not ORA-00904 itself.

The interesting part was that the identifier in the error no longer existed in the current source object.

That changes the troubleshooting question from:

What is wrong with my view?

to:

What state from the previous Data Pump execution is still being used?

For VIEWS_AS_TABLES, that distinction can save a surprising amount of time.

Practical takeaway

If Oracle Data Pump reports a column or definition that you have already corrected and independently verified:

1. verify the live source object;
2. inspect DBA_DATAPUMP_JOBS;
3. inspect or attach to the Data Pump job when more detail is needed;
4. look for temporary/internal objects left by the failed execution;
5. clean up only after proving they are stale;
6. retry from a clean dump/log state.

When Data Pump appears to remember metadata that no longer exists, investigate the previous Data Pump execution before changing the source object again.