IMPDP Fails With ORA-14148 When Moving Tables Between 10g And 12c Instances When Optimization Is In Use (Doc ID 2191835.1)

Last updated on OCTOBER 25, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Datapump import (impdp) of table data is producing errors when either the source or target table used the 12c add column optimization.

The scenario which reproduces the issue is:

- Set compatible>=12.0 on either source or target, then run on both:

create table tdp(i number);
alter table tdp add (j number default 0 not null);
alter table tdp add (k number default 0);
create unique index tdp_pk on tdp(i);

- On source export with version=10.2:
expdp DUMPFILE=tcdp.dmp CONTENT=DATA_ONLY TABLES=schema.tdp VERSION=10.2

- On target:
impdp DUMPFILE=tcdp.dmp TABLE_EXISTS_ACTION=truncate

Depending on the source and target, this will fail with one of these two messages:

ORA-00904: SYS_NC00003$: invalid identifier
ORA-14148: DML and DDL operations are not directly allowed on the guard-column.

 

Changes

The guard column is an invisible column created when adding a column with a default value in 12c.
It is created only when compatible >= 12.0 and "_add_col_optim_enabled"=TRUE. It's presence should be transparent to the user.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms