BPEL: Purging Instances-3

INTRODUCTION:

This script purges all the instance in one go from the dehydration store. This script is useful in a developement environement. It is the faster way by which you can clear the dehydration store.

Guideline for using this code:
1. Don't run this script until you are very much sure of deleting all the instances from the dehydration store.
2. First stop the BPEL instance via Enterprise manager then run this script.


truncate table cube_instance;
truncate table cube_scope;
truncate table work_item;
truncate table wi_exception;
truncate table document_ci_ref;
truncate table document_dlv_msg_ref;
truncate table scope_activation;
truncate table dlv_subscription;
truncate table audit_trail;
truncate table audit_details;
truncate table sync_trail;
truncate table sync_store;
truncate table dlv_message;
truncate table invoke_message;
truncate table ci_indexes;
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;
exec dbms_utility.analyze_schema('ORABPEL', 'Compute');


Happy Learnings....