Error Ora-14223 Deferred Segment Creation Is Not Supported For This Table During Datapump Import (Doc ID 1293326.1)

Last updated on JANUARY 22, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

The errors:

ORA-39083: Object type TABLE:"TEST"."TEST3" failed to create with error:
ORA-14223: Deferred segment creation is not supported for this table
Failing sql is:
CREATE TABLE "TEST"."TEST3" ("ID1" NUMBER, "ID2" "TEST"."T_NUMBER9_VARRAY" ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "TOOLS"


are received during DataPump import (impdp) of a table where a column was added on the source database like:

SQL> alter table test3 add id2 T_NUMBER9_VARRAY;


The following example illustrates this issue:

connect / as sysdba
create directory test as '&dump_expdp_dir';

create user tc identified by tc;
grant connect, resource, create any directory to tc;

connect tc/tc
create table test(n1 number) segment creation deferred;
create type my_type is varray(10) of number;
/

alter table test add n2 my_type;

host expdp tc/tc directory=test dumpfile=expdp_test.dmp tables=test

drop table test purge;

host impdp tc/tc directory=test dumpfile=expdp_test.dmp tables=test

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