DataPump Export (EXPDP) Hangs When Exporting Referential Constraints (Doc ID 1368586.1)

Last updated on APRIL 22, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 22-Apr-2013***

Symptoms

When performing a Data Pump export of a Peoplesoft database, the export hangs indefinitely when exporting constraints.
From analysis or from a TKPROF output from the DW* trace file,  it is determined that the hanging SQL is the following statement:

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('REF_CONSTRAINT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,
KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,
'REF_CONSTRAINT' ,KU$.OWNER_NAME
FROM
SYS.KU$_REF_CONSTRAINT_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0
AND KU$.BASE_OBJ_NUM IN (SELECT * FROM
TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND KU$.BASE_OBJ.NAME IN
(select table_name from sysadm.EXPDP_PARTS WHERE part = 'PART1') AND
KU$.OWNER_NAME IN ('SYSADM')


You have tried running the statement (adjusted) in SQLPlus and it hangs:

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('REF_CONSTRAINT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,
KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,
'REF_CONSTRAINT' ,KU$.OWNER_NAME
FROM
SYS.KU$_REF_CONSTRAINT_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0
AND KU$.OWNER_NAME IN ('SYSADM')


But, if you remove the RULE hint, the statement completes almost immediately:

SELECT SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('REF_CONSTRAINT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,
KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,
'REF_CONSTRAINT' ,KU$.OWNER_NAME
FROM
SYS.KU$_REF_CONSTRAINT_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0
AND KU$.OWNER_NAME IN ('SYSADM')

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