Expdp fails with ORA-01427

Posted by David Yahalom under Bug, Oracle

Well, here’s an interesting tidbit when using expdp I’ve noticed today. While doing a very simple export of a single schema on a 11.2.0.1 database I got the following error message:

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-01427: single-row subquery returns more than one row

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 8159

—– PL/SQL Call Stack —–
object line object
handle number name
0×7cf93e860 19028 package body SYS.KUPW$WORKER
0×7cf93e860 8191 package body SYS.KUPW$WORKER
0×7cf93e860 12728 package body SYS.KUPW$WORKER
0×7cf93e860 2425 package body SYS.KUPW$WORKER
0×7cf93e860 8846 package body SYS.KUPW$WORKER
0×7cf93e860 1651 package body SYS.KUPW$WORKER
0×7ef8a4448 2 anonymous block

Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-01427: single-row subquery returns more than one row

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 8159

This was generated by a very regular export command:

 expdp system/<PASSWORD> DIRECTORY=orabackup_nfs DUMPFILE=xxxx.DMP LOGFILE=xxxx.LOG SCHEMAS=xxxx

As it turns out, there’s a published Oracle bug on this which affects Oracle 11.2.0.1 - 11.2.0.2:

DataPump Export (expdp) Returns ORA-1427 When Partitioned IOT With Same Name Exists in More Than One Schema [ID 1064840.1]

As it turns out, the cause is if a partitioned IOT with same name exists in two different schemas. There’s any easy way to find these duplicate partitioend IOTs by running the following query as SYSDBA (change schema name to the schema you are trying to export):

select a.owner||'.'||a.table_name table1,
b.owner||'.'||b.table_name table2
from     dba_tables a,
dba_tables b
where   a.owner = 'TEST1' and
b.owner != 'TEST1' and
a.iot_type = 'IOT' and
b.iot_type = a.iot_type and
b.table_name = a.table_name and
exists (select 1
from   dba_part_tables
where  owner = a.owner and
table_name = a.table_name);

The solution provided is to either drop the IOT from one of the schemas (if possible of course), install the fix for bug 9214753 or change the KU$_IOTPART_DATA_VIEW view to a new version provided with the note in Metalink.