Purge Verification Script problem with tables MEDIATOR_RESEQUENCER_MESSAGE and MEDIATOR_GROUP_STATUS (Doc ID 2857157.1)

Last updated on SEPTEMBER 24, 2023

Applies to:

Symptoms

It is confirmed the following tables are optionally allowed for partitioning,
group2.
- MEDIATOR_RESEQUENCER_MESSAGE
- MEDIATOR_GROUP_STATUS

However, from SOAINFRA pl/sql package VERIFY_MEDIATOR, FUNCTION MR_Chk_Part_Ok, it looks like table MEDIATOR_RESEQUENCER_MESSAGE belong to verification group1A.

Ref:
https://docs.oracle.com/en/middleware/soa-suite/soa/12.2.1.4/administer/managing-database-growth.html#GUID-7B9425F5-B25E-450F-8159-829F4C61F1E8

Also in VERIFY_MEDIATOR the following function "FUNCTION MR_Chk_Part_Ok :
Check Driver Partition for MEDIATOR_RESEQUENCER_MESSAGE"
print these two messages to the log file;

UTL_FILE.Putf(log_handle, '** FAIL - TOTAL INSTANCES IN PARTITION : %s \n',mr_total_count);
UTL_FILE.Put_line(log_handle, '** FAIL - TOTAL INSTANCES STILL OPEN : ' ||mr_open_count ||' ( ' || percent_open || '% )');

The above print messages are outside of the following test, meaning the get always printed, even if no rows to be moved exists.

if mr_open_count > 0 then
MR_SUCCESS := FALSE;
percent_open := mr_open_count/mr_total_count * 100;
else
percent_open := 0;
end if;

This prevent the "partition drop.." statements to be printed which is needed during purge.

Cause

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