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 )