My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

Applies to:

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


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


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.