DataPump Export (expdp) Fails With ORA-1427 On Tables With Object Grants On Nested Tables Columns

(Doc ID 1065259.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.1 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 11-Apr-2013***

Symptoms

You started DataPump export (expdp) to extract a schema and this ends with error ORA-1427. You installed the patch for <Bug 5589140> EXPDP FAILS WITH ORA-39125, ORA-1427 but the error persists.

The next test demonstrates this:

connect / as sysdba

create or replace directory obj_grn as '/tmp';

drop user obj_grn_1 cascade;
drop user obj_grn_2 cascade;
purge dba_recyclebin;

create user obj_grn_1 identified by obj_grn_1 default tablespace users temporary tablespace temp;
create user obj_grn_2 identified by obj_grn_2 default tablespace users temporary tablespace temp;

grant connect, resource to obj_grn_1, obj_grn_2;
grant read, write on directory obj_grn to obj_grn_1;

connect obj_grn_1/obj_grn_1

create type a_type as object
(
   row_no number,
   text   varchar2(10)
)
/

create type a_type_tab as table of a_type
/

create table a_tab
(
   id    number,
   texts a_type_tab
)
nested table texts store as nested_texts;

-- object grant on nested table column
grant insert (texts) on a_tab to obj_grn_2;

-- delete dmp/log files, if any
host rm /tmp/obj_grn_1.*

-- start schema export
host expdp obj_grn_1/obj_grn_1 directory=obj_grn dumpfile=obj_grn_1.dmp logfile=obj_grn_1.log schemas=obj_grn_1

This returns:

...
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE:"OBJ_GRN_1"."A_TAB"]
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6313
...

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