solution: 11gR2 expdp might fail with ORA-1427 when exporting partitioned IOT

Just for your information. in 11gR2 a datapump export of a partitioned IOT
might fail with following error:


Starting "SCOTT"."SYS_EXPORT_SCHEMA_03":  USERID=scott/********
schemas=scott DIRECTORY=RESTORE_scott COMPRESSION=all REUSE_DUMPFILES=Y
LOGFILE=expdp_scott.bck.log dumpfile=expdp_scott.bck.dmp
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

this is Bug 9214753: EXPDP RETURNS ORA-1427 WHEN SAME PARTITIONED IOT EXISTS
IN EXPORTING SCHEMA

solution is to add following line in subselect in select list of view
ku$_iotpart_data_view
(created in catmeta.sql):
create or replace force view ku$_iotpart_data_view of ku$_table_data_t
with object OID(obj_num)
as select '1','2',
ip.obj#,
(select o1.obj#
from obj$ o1
where o1.name=bo.name
and o1.subname=o.subname
and o1.owner#=o.owner_num    -- <<<< fix for bug 9214753
  )
,o.subname,
NULL,
...

hth,
Andre

Other related posts:

  • » solution: 11gR2 expdp might fail with ORA-1427 when exporting partitioned IOT - Andre van Winssen