Merciless Purging of SOAINFRA schema in Oracle SOA Suite 11g:
-- Start of Purge Script
----------------------------------
-- Purge the MEDIATOR data
----------------------------------
delete from mediator_case_instance;
delete from mediator_audit_document;
delete from mediator_callback;
delete from mediator_group_status;
delete from mediator_payload;
delete from mediator_deferred_message;
delete from mediator_resequencer_message;
delete from mediator_case_detail;
delete from mediator_correlation;
delete from mediator_instance;
commit;
----------------------------
-- Purge the BPEL data
----------------------------
delete from headers_properties;
delete from ag_instance;
delete from audit_counter;
delete from audit_trail;
delete from audit_details;
delete from ci_indexes;
delete from work_item;
delete from wi_fault;
delete from xml_document_ref;
delete from xml_document;
delete from document_dlv_msg_ref;
delete from document_ci_ref;
delete from dlv_subscription;
delete from dlv_message;
delete from rejected_msg_native_payload;
delete from instance_payload;
delete from test_details;
delete from cube_scope;
delete from cube_instance;
commit;
---------------------------
-- Purge the BPM data
---------------------------
delete from bpm_audit_query;
delete from bpm_measurement_actions;
delete from bpm_measurement_action_exceps;
delete from bpm_cube_auditinstance;
delete from bpm_cube_taskperformance;
delete from bpm_cube_processperformance;
commit;
------------------------------------
-- Purge the WORKFLOW data
-- ---------------------------------
delete from wftask_tl;
delete from wftaskhistory;
delete from wftaskhistory_tl;
delete from wfcomments;
delete from wfmessageattribute;
delete from wfattachment;
delete from wfassignee;
delete from wfreviewer;
delete from wfcollectiontarget;
delete from wfroutingslip;
delete from wfnotification;
delete from wftasktimer;
delete from wftaskerror;
delete from wfheaderprops;
delete from wfevidence;
delete from wftaskassignmentstatistic;
delete from wftaskaggregation;
delete from wftask;
commit;
------------------------------------
-- Purge the COMPOSITE data
------------------------------------
delete from composite_sensor_value;
delete from composite_instance_assoc;
delete from attachment;
delete from attachment_ref;
delete from composite_instance_fault;
delete from reference_instance;
delete from component_instance;
delete from composite_instance;
commit;
------------------------
-- Reclaim disk space
------------------------
alter table mediator_case_instance enable row movement;
alter table mediator_case_instance shrink space;
alter table mediator_case_instance disable row movement;
alter table mediator_audit_document enable row movement;
alter table mediator_audit_document shrink space;
alter table mediator_audit_document disable row movement;
alter table mediator_callback enable row movement;
alter table mediator_callback shrink space;
alter table mediator_callback disable row movement;
alter table mediator_group_status enable row movement;
alter table mediator_group_status shrink space;
alter table mediator_group_status disable row movement;
alter table mediator_payload enable row movement;
alter table mediator_payload shrink space;
alter table mediator_payload disable row movement;
alter table mediator_deferred_message enable row movement;
alter table mediator_deferred_message shrink space;
alter table mediator_deferred_message disable row movement;
alter table mediator_resequencer_message enable row movement;
alter table mediator_resequencer_message shrink space;
alter table mediator_resequencer_message disable row movement;
alter table mediator_case_detail enable row movement;
alter table mediator_case_detail shrink space;
alter table mediator_case_detail disable row movement;
alter table mediator_correlation enable row movement;
alter table mediator_correlation shrink space;
alter table mediator_correlation disable row movement;
alter table mediator_instance enable row movement;
alter table mediator_instance shrink space;
alter table mediator_instance disable row movement;
alter table headers_properties enable row movement;
alter table headers_properties shrink space;
alter table headers_properties disable row movement;
alter table ag_instance enable row movement;
alter table ag_instance shrink space;
alter table ag_instance disable row movement;
alter table audit_counter enable row movement;
alter table audit_counter shrink space;
alter table audit_counter disable row movement;
alter table audit_trail enable row movement;
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;
alter table audit_details disable row movement;
alter table ci_indexes enable row movement;
alter table ci_indexes shrink space;
alter table ci_indexes disable row movement;
alter table work_item enable row movement;
alter table work_item shrink space;
alter table work_item disable row movement;
alter table wi_fault enable row movement;
alter table wi_fault shrink space;
alter table wi_fault disable row movement;
alter table xml_document_ref enable row movement;
alter table xml_document_ref shrink space;
alter table xml_document_ref disable row movement;
alter table document_dlv_msg_ref enable row movement;
alter table document_dlv_msg_ref shrink space;
alter table document_dlv_msg_ref disable row movement;
alter table document_ci_ref enable row movement;
alter table document_ci_ref shrink space;
alter table document_ci_ref disable row movement;
alter table dlv_subscription enable row movement;
alter table dlv_subscription shrink space;
alter table dlv_subscription disable row movement;
alter table dlv_message enable row movement;
alter table dlv_message shrink space;
alter table dlv_message disable row movement;
alter table rejected_msg_native_payload enable row movement;
alter table rejected_msg_native_payload shrink space;
alter table rejected_msg_native_payload disable row movement;
alter table instance_payload enable row movement;
alter table instance_payload shrink space;
alter table instance_payload disable row movement;
alter table test_details enable row movement;
alter table test_details shrink space;
alter table test_details disable row movement;
alter table cube_scope enable row movement;
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;
alter table cube_instance disable row movement;
alter table bpm_audit_query enable row movement;
alter table bpm_audit_query shrink space;
alter table bpm_audit_query disable row movement;
alter table bpm_measurement_actions enable row movement;
alter table bpm_measurement_actions shrink space;
alter table bpm_measurement_actions disable row movement;
alter table bpm_measurement_action_exceps enable row movement;
alter table bpm_measurement_action_exceps shrink space;
alter table bpm_measurement_action_exceps disable row movement;
alter table bpm_cube_auditinstance enable row movement;
alter table bpm_cube_auditinstance shrink space;
alter table bpm_cube_auditinstance disable row movement;
alter table bpm_cube_taskperformance enable row movement;
alter table bpm_cube_taskperformance shrink space;
alter table bpm_cube_taskperformance disable row movement;
alter table bpm_cube_processperformance enable row movement;
alter table bpm_cube_processperformance shrink space;
alter table bpm_cube_processperformance disable row movement;
alter table wftask_tl enable row movement;
alter table wftask_tl shrink space;
alter table wftask_tl disable row movement;
alter table wftaskhistory enable row movement;
alter table wftaskhistory shrink space;
alter table wftaskhistory disable row movement;
alter table wftaskhistory_tl enable row movement;
alter table wftaskhistory_tl shrink space;
alter table wftaskhistory_tl disable row movement;
alter table wfcomments enable row movement;
alter table wfcomments shrink space;
alter table wfcomments disable row movement;
alter table wfmessageattribute enable row movement;
alter table wfmessageattribute shrink space;
alter table wfmessageattribute disable row movement;
alter table wfattachment enable row movement;
alter table wfattachment shrink space;
alter table wfattachment disable row movement;
alter table wfassignee enable row movement;
alter table wfassignee shrink space;
alter table wfassignee disable row movement;
alter table wfreviewer enable row movement;
alter table wfreviewer shrink space;
alter table wfreviewer disable row movement;
alter table wfcollectiontarget enable row movement;
alter table wfcollectiontarget shrink space;
alter table wfcollectiontarget disable row movement;
alter table wfroutingslip enable row movement;
alter table wfroutingslip shrink space;
alter table wfroutingslip disable row movement;
alter table wfnotification enable row movement;
alter table wfnotification shrink space;
alter table wfnotification disable row movement;
alter table wftasktimer enable row movement;
alter table wftasktimer shrink space;
alter table wftasktimer disable row movement;
alter table wftaskerror enable row movement;
alter table wftaskerror shrink space;
alter table wftaskerror disable row movement;
alter table wfheaderprops enable row movement;
alter table wfheaderprops shrink space;
alter table wfheaderprops disable row movement;
alter table wfevidence enable row movement;
alter table wfevidence shrink space;
alter table wfevidence disable row movement;
DROP INDEX WFTASKASSIGNMENTSTATISTICID_I;
DROP INDEX WFTASKASSTARTENDDATE_I;
DROP INDEX WFTASKASASSIGNEETYPE_I;
alter table wftaskassignmentstatistic enable row movement;
alter table wftaskassignmentstatistic shrink space;
alter table wftaskassignmentstatistic disable row movement;
CREATE INDEX "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTICID_I" ON "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTIC" ("TASKID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."WFTASKASSTARTENDDATE_I" ON "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTIC" ("STARTDATE", "ENDDATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."WFTASKASASSIGNEETYPE_I" ON "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTIC" (LOWER("ASSIGNEE"), "ASSIGNEETYPE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
alter table wftaskaggregation enable row movement;
alter table wftaskaggregation shrink space;
alter table wftaskaggregation disable row movement;
alter table wftask enable row movement;
alter table wftask shrink space;
alter table wftask disable row movement;
alter table composite_sensor_value enable row movement;
alter table composite_sensor_value shrink space;
alter table composite_sensor_value disable row movement;
alter table composite_instance_assoc enable row movement;
alter table composite_instance_assoc shrink space;
alter table composite_instance_assoc disable row movement;
alter table attachment enable row movement;
alter table attachment shrink space;
alter table attachment disable row movement;
alter table attachment_ref enable row movement;
alter table attachment_ref shrink space;
alter table attachment_ref disable row movement;
DROP INDEX COMPOSITE_INSTANCE_FAULT_CIDN;
DROP INDEX COMPOSITE_INSTANCE_FAULT_CT_EC;
DROP INDEX COMPOSITE_INSTANCE_FAULT_EC_CT;
DROP INDEX COMPOSITE_INSTANCE_FAULT_ECID;
alter table composite_instance_fault enable row movement;
alter table composite_instance_fault shrink space;
alter table composite_instance_fault disable row movement;
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_CIDN" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("COMPOSITE_DN", "ERROR_CATEGORY") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_CT_EC" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("CREATED_TIME" DESC, "ERROR_CATEGORY") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_EC_CT" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("ERROR_CATEGORY", "CREATED_TIME" DESC) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_ECID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("ECID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
DROP INDEX REFERENCE_INSTANCE_CDN_STATE;
DROP INDEX REFERENCE_INSTANCE_CO_ID;
DROP INDEX REFERENCE_INSTANCE_ECID;
DROP INDEX REFERENCE_INSTANCE_ID;
DROP INDEX REFERENCE_INSTANCE_STATE;
DROP INDEX REFERENCE_INSTANCE_TIME_CDN;
alter table reference_instance enable row movement;
alter table reference_instance shrink space;
alter table reference_instance disable row movement;
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_CDN_STATE" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("COMPOSITE_DN", "STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_CO_ID" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("PROTOCOL_CORRELATION_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_ECID" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("ECID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_ID" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_STATE" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_TIME_CDN" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("CREATED_TIME" DESC, "COMPOSITE_DN", "STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
alter table component_instance enable row movement;
alter table component_instance shrink space;
alter table component_instance disable row movement;
DROP INDEX COMPOSITE_INSTANCE_CIDN;
DROP INDEX COMPOSITE_INSTANCE_CO_ID;
DROP INDEX COMPOSITE_INSTANCE_CREATED;
DROP INDEX COMPOSITE_INSTANCE_ECID;
DROP INDEX COMPOSITE_INSTANCE_ID;
DROP INDEX COMPOSITE_INSTANCE_STATE;
alter table composite_instance enable row movement;
alter table composite_instance shrink space;
alter table composite_instance disable row movement;
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_CIDN" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("COMPOSITE_DN", "STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_CO_ID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("CONVERSATION_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_CREATED" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("CREATED_TIME" DESC, "COMPOSITE_DN") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_ECID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("ECID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_ID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_STATE" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
alter table audit_details modify lob (bin) (shrink space);
alter table composite_instance_fault modify lob (error_message) (shrink space);
alter table composite_instance_fault modify lob (stack_trace) (shrink space);
alter table cube_scope modify lob (scope_bin) (shrink space);
alter table reference_instance modify lob (error_message) (shrink space);
alter table reference_instance modify lob (stack_trace) (shrink space);
alter table test_definitions modify lob (definition) (shrink space);
alter table wi_fault modify lob (message) (shrink space);
alter table xml_document modify lob (document) (shrink space);
-- The below statements cannot run on Oracle XE
alter index ad_pk rebuild online;
alter index at_pk rebuild online;
alter index ci_creation_date rebuild online;
alter index ci_custom3 rebuild online;
alter index ci_ecid rebuild online;
alter index ci_name_rev_state rebuild online;
alter index ci_pk rebuild online;
alter index composite_instance_cidn rebuild online;
alter index composite_instance_co_id rebuild online;
alter index composite_instance_created rebuild online;
alter index composite_instance_ecid rebuild online;
alter index composite_instance_id rebuild online;
alter index composite_instance_state rebuild online;
alter index cs_pk rebuild online;
alter index dm_conversation rebuild online;
alter index dm_pk rebuild online;
alter index doc_dlv_msg_guid_index rebuild online;
alter index doc_store_pk rebuild online;
alter index ds_conversation rebuild online;
alter index ds_conv_state rebuild online;
alter index ds_fk rebuild online;
alter index ds_pk rebuild online;
alter index header_properties_pk rebuild online;
alter index instance_payload_key rebuild online;
alter index reference_instance_cdn_state rebuild online;
alter index reference_instance_co_id rebuild online;
alter index reference_instance_ecid rebuild online;
alter index reference_instance_id rebuild online;
alter index reference_instance_state rebuild online;
alter index reference_instance_time_cdn rebuild online;
alter index state_type_date rebuild online;
alter index wf_crdate_cikey rebuild online;
alter index wf_crdate_type rebuild online;
alter index wf_fk2 rebuild online;
alter index wifault_pk rebuild online;
alter index wi_expired rebuild online;
alter index wi_key_crdate_state rebuild online;
alter index wi_pk rebuild online;
alter index wi_stranded rebuild online;
alter index xml_doc_reference_pk rebuild online;
commit;
--- End of Purge script
