DataPump Export (expdp) Returns ORA-1427 When Partitioned IOTs With Same Name Exist in More Than One Schema (Doc ID 1064840.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1.0 to 11.2.0.1.0 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 23-MAY-2012***

Symptoms

You performed a schema export using DataPump utility (expdp). If partitioned IOTs with same name exist in two different schemas, then expdp returns:

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-1427: single-row subquery returns more than one row
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-6512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
486ad7d80 19028 package body SYS.KUPW$WORKER
486ad7d80 8191 package body SYS.KUPW$WORKER
486ad7d80 12728 package body SYS.KUPW$WORKER
486ad7d80 2425 package body SYS.KUPW$WORKER
486ad7d80 8846 package body SYS.KUPW$WORKER
486ad7d80 1651 package body SYS.KUPW$WORKER
4924fee18 2 anonymous block

This reproduces with Oracle version 11.2.0.1. Does not reproduce with 11.1.0.7 or older.

Here an example that demonstrates this:

connect / as sysdba
drop user test1 cascade;
drop user test2 cascade;
purge dba_recyclebin;

create user test1 identified by test1 default tablespace users temporary tablespace temp;
create user test2 identified by test2 default tablespace users temporary tablespace temp;
grant connect, resource to test1, test2;

create or replace directory test as '/tmp';

connect test1/test1

-- create partitioned IOT
create table test_iot
(
   id number not null,
   text varchar2(10),
   constraint pk_index primary key (id)
)
organization index
partition by range (id)
(
   partition p1 values less than (10),
   partition p2 values less than (100),
   partition p3 values less than (maxvalue)
);

connect test2/test2

-- create same partitioned IOT as in schema TEST1
create table test_iot
(
   id number not null,
   text varchar2(10),
   constraint pk_index primary key (id)
)
organization index
partition by range (id)
(
   partition p1 values less than (10),
   partition p2 values less than (100),
   partition p3 values less than (maxvalue)
);

-- perform expdp
host expdp system/<password> directory=test dumpfile=test1.dmp schemas=test1 logfile=test1.log

This will raise:

...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-1427: single-row subquery returns more than one row
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-6512: at "SYS.KUPW$WORKER", line 8159
...

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