Managing, Purging and Cleanup of the BPEL Instances (2)

As promised in my last post, I am posting the bpel_dehyd_manage.sql script. The script will alter the tables, deallocate unused data space, alter the indexes and call the purge_instances procedure.

set echo onset time onset timing on
spool bpel_dehyd_manage.log

-- Call the purge instance procdure to truncate the data from the BPEL Instances tables
exec purge_instances(SYSDATE-&1);

-- The following statements reclaims the deleted data space
alter table cube_instance deallocate unused;
alter table cube_scope deallocate unused;
alter table work_item deallocate unused;
alter table wi_exception deallocate unused;
alter table document_ci_ref deallocate unused;
alter table document_dlv_msg_ref deallocate unused;
alter table scope_activation deallocate unused;
alter table dlv_subscription deallocate unused;
alter table audit_trail deallocate unused;
alter table audit_details deallocate unused;
alter table sync_trail deallocate unused;
alter table sync_store deallocate unused;
alter table dlv_message deallocate unused;
alter table invoke_message deallocate unused;
alter table ci_indexes deallocate unused;

alter table cube_scope enable row movement;
alter table cube_scope shrink space compact;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;

alter table cube_instance enable row movement;
alter table cube_instance shrink space compact;
alter table cube_instance shrink space;
alter table cube_instance disable row movement;

alter table work_item enable row movement;
alter table work_item shrink space compact;
alter table work_item shrink space;
alter table work_item disable row movement;

alter table wi_exception enable row movement;
alter table wi_exception shrink space compact;
alter table wi_exception shrink space;
alter table wi_exception disable row movement;

alter table document_ci_ref enable row movement;
alter table document_ci_ref shrink space compact;
alter table document_ci_ref shrink space;
alter table document_ci_ref disable row movement;

alter table document_dlv_msg_ref enable row movement;
alter table document_dlv_msg_ref shrink space compact;
alter table document_dlv_msg_ref shrink space;
alter table document_dlv_msg_ref disable row movement;

alter table scope_activation enable row movement;
alter table scope_activation shrink space compact;
alter table scope_activation shrink space;
alter table scope_activation disable row movement;

alter table dlv_subscription enable row movement;
alter table dlv_subscription shrink space compact;
alter table dlv_subscription shrink space;
alter table dlv_subscription disable row movement;

alter table audit_trail enable row movement;
alter table audit_trail shrink space compact;
alter table audit_trail shrink space;
alter table audit_trail disable row movement;

alter table audit_details enable row movement;
alter table audit_details shrink space compact;
alter table audit_details shrink space;
alter table audit_details disable row movement;

alter table sync_trail enable row movement;
alter table sync_trail shrink space compact;
alter table sync_trail shrink space;
alter table sync_trail disable row movement;

alter table sync_store enable row movement;
alter table sync_store shrink space compact;
alter table sync_store shrink space;
alter table sync_store disable row movement;

alter table invoke_message enable row movement;
alter table invoke_message shrink space compact;
alter table invoke_message shrink space;
alter table invoke_message disable row movement;

alter table dlv_message enable row movement;
alter table dlv_message shrink space compact;
alter table dlv_message shrink space;
alter table dlv_message disable row movement;

alter table ci_indexes enable row movement;
alter table ci_indexes shrink space compact;
alter table ci_indexes shrink space;
alter table ci_indexes disable row movement;

alter table XML_DOCUMENT enable row movement;
alter table XML_DOCUMENT shrink space compact;
alter table XML_DOCUMENT shrink space;
alter table XML_DOCUMENT disable row movement;

-- Rebuild the indexes of the ORABPEL schema for faster lookups
spool off
set termout off
set heading off
set echo off
set time off
set timing off

spool rebuild_indexes.sql

select 'Alter Index 'index_name' Rebuild;' from from user_indexes where table_name not like 'BIN$%' and index_type <> 'LOB';

spool off

set termout on
set echo on

spool rebuild_indexes.log
@rebuild_indexes.sql

spool off
spool off

The above table will manage you BPEL instances and you may schedule this script run based on your organization or client requirement.

There is one more tip for the dehydation store of the BPEL processes - move the LOB Columns to non assm tablespaces to eliminate contention issues and allow indexing for the CUBE_SCOPE,
AUDIT_DETAILS, SYNC_STORE and XML_DOCUMENT tables in the ORABPEL schema. You may a blog entry on how to perform the above operation,

http://scsoablog.blogspot.com/2008/02/more-hw-contention-tuning.html
http://scsoablog.blogspot.com/2008/01/optimizing-rac-performance-for-soa-part.html

Cheers!