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 MARCH 06, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.1 [Release 10.1 to 11.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.


NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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
...

Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References

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