After a day spent on Metalink searching for patches and solutions we opened SR. Oracle Support recommended patch no. 9645701 (note ID 1131775.1; this patch is part of 11.1.1.3).
The main problem was purging of couple of major tables: xml_document, dlv_message, document_dlv_msg_ref.
However, patching didn't bring solution for orphaned messages left behind by buggy 11.1.1.2 scripts.
Since I was in-charge for the database (in general), role to dive into into purge scripts which come with Repository Creation Utility was mine.
After couple of queries, few major tests (God bless big teams: lot of developers' schemes for testing!), I released purge bridge: one-time shot script which should be used to remove "junk" left by .2 scripts.
In order to check critical tables affected by instance growth in your DB, execute diagnostic.sql (I'd be happy to get results from your environment; feel free to mail them to me).
-- extract distinct states
select distinct (case when state=0 then 'running'
when state=1 then 'recovery required'
when state=2 then 'completed successfully'
when state=3 then 'faulted'
when state=4 then 'terminated by user'
when state=5 then 'suspended'
when state=6 then 'stale'
else state || ''
end) as states_from_comp_inst
from composite_instance;
-- extract number of instances per state
select case when state=0 then 'running'
when state=1 then 'recovery required'
when state=2 then 'completed successfully'
when state=3 then 'faulted'
when state=4 then 'terminated by user'
when state=5 then 'suspended'
when state=6 then 'stale'
else state || ''
end, count(*) as num_of_comp_inst
from composite_instance group by state;
-- extract distinct states
select distinct (case when state=1 then 'running'
when state=2 then 'open suspended'
when state=3 then 'open faulted'
when state=5 then 'completed'
when state=6 then 'closed faulted'
when state=8 then 'aborted'
when state=9 then 'closed stale'
else state || ''
end) as state_from_cube_inst
from cube_instance;
-- extract number grouped by state
select case when state=1 then 'running'
when state=2 then 'open suspended'
when state=3 then 'open faulted'
when state=5 then 'completed'
when state=6 then 'closed faulted'
when state=8 then 'aborted'
when state=9 then 'closed stale'
else state || ''
end, count(*) as num_of_cube_inst
from cube_instance group by state;
select 'AUDIT_TRAIL', count(*) as audit_trail_num from audit_trail;
select 'CUBE_SCOPE', count(*) as cube_scope_num from cube_scope;
select 'CI_INDEXES', count(*) as ci_indexes_num from ci_indexes where cikey not in ( select cikey from cube_instance );
select 'WORK_ITEM', count(*) as work_item_num from work_item;
select 'WI_FAULT', count(*) as wi_fault_num from wi_fault;
select 'AUDIT_DETAILS', count(*) as audit_details_num from audit_details;
select 'TEST_DETAILS', count(*) as test_details_num from test_details;
select 'DOCUMENT_CI_REF', count(*) as document_ci_ref_num from document_ci_ref;
select 'AG_INSTANCE', count(*) as ag_instance_num from ag_instance;
select 'DLV_SUBSCRIPTION', count(*) as dlv_subscription_num from dlv_subscription;
select 'XML_DOCUMENT' as n, a.xml_document_num as total, b.xml_document_orph_num as orph, 100*b.xml_document_orph_num/a.xml_document_num as "%"
from
( select count(*) as xml_document_num from xml_document ) a,
(select count(*) as xml_document_orph_num from xml_document where document_id not in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
and document_id not in ( select payload_key from instance_payload ))b
union all
select 'DOCUMENT_DLV_MSG_REF' as n, a.xml_document_dlv_msg_ref_num, b.xml_doc_dlv_msg_ref_orp_num, 100*b.xml_doc_dlv_msg_ref_orp_num/a.xml_document_dlv_msg_ref_num as perc_of_orph
from
(select count(*) as xml_document_dlv_msg_ref_num from document_dlv_msg_ref) a,
(select count(*) as xml_doc_dlv_msg_ref_orp_num from document_dlv_msg_ref where message_guid not in
(select m.message_guid from dlv_message m, cube_instance c
where m.cikey=c.cikey)) b
union all
select 'XML_DOCUMENT_REF' as n, a.xml_document_ref_num, b.xml_document_ref_orph_num, 100*b.xml_document_ref_orph_num/a.xml_document_ref_num as perc_of_orph
from
(select count(*) as xml_document_ref_num from xml_document_ref) a,
(select count(*) as xml_document_ref_orph_num from xml_document_ref where document_id not in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
and document_id not in ( select payload_key from instance_payload )) b
union all
select 'HEADERS_PROPERTIES' as n, a.headers_properties_num, b.headers_properties_orph_num, 100*b.headers_properties_orph_num/a.headers_properties_num as perc_of_orph
from
(select count(*) as headers_properties_num from headers_properties) a,
(select count(*) as headers_properties_orph_num from headers_properties where message_guid in
(select m.message_guid from dlv_message m where m.cikey not in ( select cikey from cube_instance ))) b
union all
select 'DLV_MESSAGE' as n, a.dlv_message_num, b.dlv_message_orph_num, 100*b.dlv_message_orph_num/a.dlv_message_num as perc_of_orph
from
(select count(*) as dlv_message_num from dlv_message) a,
(select count(*) as dlv_message_orph_num from dlv_message where cikey not in ( select cikey from cube_instance )) b;
After this, you can execute clean.sql.
/*
*
Script which purges the tables impacted by instance data growth as documented on:
http://download.oracle.com/docs/cd/E14571_01/core.1111/e10108/bpel.htm#BABFBDGF
The script removes junk left over by 11.1.1.2 purge scripts
*/
create table xml_document_x as
select * from xml_document where document_id in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
or document_id in ( select payload_key from instance_payload );
alter table b2b_data_storage disable constraint b2b_ds_doc_id_fk;
truncate table xml_document;
alter table b2b_data_storage enable constraint b2b_ds_doc_id_fk;
insert into xml_document select * from xml_document_x;
commit;
drop table xml_document_x;
create table xml_document_ref_x as
select * from xml_document_ref where document_id in
(select document_id from document_dlv_msg_ref r, dlv_message m, cube_instance c
where r.message_guid=m.message_guid and m.cikey=c.cikey)
or document_id in ( select payload_key from instance_payload );
truncate table xml_document_ref;
insert into xml_document_ref select * from xml_document_ref_x;
commit;
drop table xml_document_ref_x;
create table document_dlv_msg_ref_x as
select * from document_dlv_msg_ref where message_guid in
(select m.message_guid from dlv_message m, cube_instance c
where m.cikey=c.cikey);
truncate table document_dlv_msg_ref;
insert into document_dlv_msg_ref select * from document_dlv_msg_ref_x;
commit;
drop table document_dlv_msg_ref_x;
create table headers_properties_x as
select * from headers_properties where message_guid in (select m.message_guid from dlv_message m where m.cikey in ( select cikey from cube_instance ));
commit;
truncate table headers_properties;
insert into headers_properties select * from headers_properties_x;
commit;
drop table headers_properties_x;
create table dlv_message_x as
select * from dlv_message where cikey in ( select cikey from cube_instance );
commit;
truncate table dlv_message;
insert into dlv_message select * from dlv_message_x;
drop table dlv_message_x;
purge recyclebin;
Note: make backup :)
Cheers.
