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

From: Andre van Winssen <dreveewee_at_gmail.com>
Date: Wed, 27 Jan 2010 07:59:52 +0100
Message-ID: <9b46ac491001262259h5b50b50fj4b6b38f103fc5917_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 27 2010 - 00:59:52 CST