ORA-24067: exceeded maximum number of subscribers for queue | ORA-39257 ORA-39077 ORA-31693 ORA-29913

Hi,

When you start an import operation, you can get ” ORA-24067: exceeded maximum number of subscribers for queue | ORA-39257 ORA-39077 ORA-31693 ORA-29913 ” errors.

ORA-24067: exceeded maximum number of subscribers for queue

Error details are as follows.

ORA-39257: internal error: [kupuatjq1] [SYS_IMPORT_TABLE_03] [19] [MSDBA] [11] [0x7f48851f18d4] [39077]
ORA-39077: unable to subscribe agent KUPC$A_1_093756298515000 to queue "KUPC$C_4_20201024000819_0"
ORA-24067: exceeded maximum number of subscribers for queue SYS.KUPC$C_4_20201024000819_0
ORA-31693: Table data object "MSDBA"."TEST_TABLE":"PART_20200918"."SPART_20200918_006" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-39257: internal error

ORA-39077: unable to subscribe agent

ORA-24067: exceeded maximum number of subscribers for queue

ORA-31693: Table data object

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

This error ORA-39257 ORA-39077 ORA-24067 ORA-31693 ORA-29913 is related with the Parallel parameter, to solve this problem, perform export import paralel 1 ( serial )

Once the sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N exceeds 10000, the name of a rule set then exceeds 30 characters, which should not be a problem any more with 19c version.

The max identifier length with DB compatibility set to < 12.2  is  30
The max identifier length with DB compatibility set to >= 12.2  is  128

The issue in this case was caused due to compatible setting 12.1.0.2.0, in which case the name of a rule set then cannot exceed 30 characters.

Set compatible parameter to be>= 12. 2 to accommodate identifier length upto 128.

Bug:22862597 : ORA-39077 AND ORA-24067 DURING DATA PUMP IMPORT
Max subscriber queue error when running Data Pump with parallel > 1

Bug:22862597 is fixed in 12.2.

Possible Workaround:  Run Data Pump without parallel.

NOTE:

With the fix for Bug 22862597 if the error ORA-24067 occurs, it could be related to number of subscribers for queue has exceeded 1024 limit.
Possible workaround is to run Data Pump with parameter parallel value in 1/2 until it works or until we get to 1.

You can read the following article, if you want to learn more details about Export – import.

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )