DataPump Export (EXPDP) Errors ORA-39126 ORA-1427 Returns More Than One Row When Exporting Materialized Views (Doc ID 1909855.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Enterprise Manager for Oracle Database - Version 12.1.0.1.0 to 12.1.0.1.0 [Release 12.1]
Information in this document applies to any platform.

Symptoms

1. You run a full database Export Data Pump job in Oracle12c Release 12.1.0.1 and the DataPump job aborts with the following errors:

#> expdp system/password directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log reuse_dumpfiles=y full=y

Export: Release 12.1.0.1.0 - Production on Mon Jul 21 10:57:30 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log reuse_dumpfiles=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 81.84 GB
...
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TRIGGER:"SCOTT"."MY_TRIGGER"]
ORA-01427: single-row subquery returns more than one row

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11014

----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
0x8626cf00     26217  package body SYS.KUPW$WORKER
0x8626cf00     11041  package body SYS.KUPW$WORKER
0x8626cf00     13189  package body SYS.KUPW$WORKER
0x8626cf00      3128  package body SYS.KUPW$WORKER
0x8626cf00     11737  package body SYS.KUPW$WORKER
0x8626cf00      2059  package body SYS.KUPW$WORKER
0xe6ef7a18         2  anonymous block

KUPF$FILE.WRITE_LOB
KUPF$FILE.WRITE_LOB
MD FilePieces Count: 1
FORALL
FORALL
DBMS_LOB.TRIM
DBMS_LOB.TRIM
DBMS_METADATA.FETCH_XML_CLOB
DBMS_METADATA.FETCH_XML_CLOB
In procedure DETERMINE_FATAL_ERROR
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at Mon Jul 21 11:13:27 2014 elapsed 0 00:15:55

2. Although you expect the that problem is related to triggers, you can successfully export the triggers with Export DataPump.

#> expdp system/password directory=my_dir dumpfile=expdp_trig.dmp logfile=expdp_trig.log reuse_dumpfiles=y full=y include=trigger

Export: Release 12.1.0.1.0 - Production on Tue Jul 22 12:21:29 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=my_dir dumpfile=expdp_trig.dmp logfile=expdp_trig.log reuse_dumpfiles=y full=y include=trigger
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /expdp/expdp_trig.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Jul 22 12:22:09 2014 elapsed 0 00:27:36

3. You can confirm that the problem reproduces when you export materialized views:

#> expdp system/password directory=my_dir dumpfile=expdp_mv.dmp logfile=expdp_mv.log reuse_dumpfiles=y full=y include=materialized_view

Export: Release 12.1.0.1.0 - Production on Tue Jul 22 12:26:29 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=my_dir dumpfile=expdp_mv.dmp logfile=expdp_mv.log reuse_dumpfiles=y full=y include=materialized_view
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS []
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 11009
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
0x8626cf00     26217  package body SYS.KUPW$WORKER
0x8626cf00     11041  package body SYS.KUPW$WORKER
0x8626cf00     13189  package body SYS.KUPW$WORKER
0x8626cf00      3128  package body SYS.KUPW$WORKER
0x8626cf00     11737  package body SYS.KUPW$WORKER
0x8626cf00      2059  package body SYS.KUPW$WORKER
0x532cc7790         2  anonymous block
...
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at Tue Jul 22 12:27:20 2014 elapsed 0 00:00:45

4. The problem also reproduces when you try to obtain the DDL from a materialized view (which selects from a remote database over a database link):

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