Delete bulk BPEL instances

It can take a while to purge the BPEL instances from the dehydration store. It can be done faster, if you would like. The following step is usefull in a developement environement. First stop the BPEL instance via Enterprise manager.

BTW: If you do not want a bulk delete, but instance by instance for a particular date and domain and revision, look here.

Then in sqlplus orabpel/password (note: 10.1.3):

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');

Then in sqlplus orabpel/password (note: 10.1.2.0.2)

truncate table cube_instance;
truncate table cube_scope;
truncate table work_item;
truncate table wi_exception;
truncate table document;
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 tx_inferior;
truncate table tx_superior;
truncate table dlv_message;
truncate table dlv_message_bin;
truncate table invoke_message;
truncate table invoke_message_bin;

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 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 tx_inferior deallocate unused;
alter table tx_superior deallocate unused;
alter table dlv_message deallocate unused;
alter table dlv_message_bin deallocate unused;
alter table invoke_message deallocate unused;
alter table invoke_message_bin 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');