Delete/Purge the data from Orabpel Schema analysis stats

There are three possible way to delete/purge the data from orabpel schema.

1)       Delete Option at Instance Tab Page – At BPEL Console instance tab page user can select all the instances and can delete it but this option is not useful for deleting data in bulk. User can delete only 1000 instance at a time.

2)       Delete all Instance at Administration Tab Page – At BPEL console>>Administration>>Delete All Instance – This option will delete all the instances from Orabpel schema.

3)       Customize or Oracle purge script – Oracle provide the purge script for orabpel schema which is located at $SOAHOME\SOASuite101351\bpel\system\database\scripts\purge_instances_oracle.sql which will delete everything as per sql script definition.

I have tried all above three option in my test environment and want to share my result stats using this post –

Orabpel Schema imp. Tables (task related tables excluded)	RelationShip Key	Instance Tab Page	Administration Tab Page	Custom Purging or Oracle SQL
wi_fault	CIKEY	Not Deleted	Not Deleted	Deleted
audit_trail	CIKEY	Deleted	Deleted	Deleted
audit_details	CIKEY	Deleted	Deleted	Deleted
work_item	CIKEY	Deleted	Deleted	Deleted
ci_indexes	CIKEY	Not Deleted	Not Deleted	Deleted
cube_instance	CIKEY	Deleted	Deleted	Deleted
cube_scope	CIKEY	Deleted	Deleted	Deleted
dlv_subscription	CIKEY	Deleted	Deleted	Deleted
dlv_message	MASSAGE_GUID	Not Deleted	Deleted	Deleted
document_dlv_msg_ref	MASSAGE_GUID, DOCKEY	Not Deleted	Deleted	Deleted
invoke_message	MASSAGE_GUID	Not Deleted	Deleted	Deleted
xml_document	DOCKEY	Not Deleted	Deleted	Deleted

As stated above many tables which related to delivery and invoke message metadata, and xml document, records for those tables won’t get deleted using delete option at instance tab page, Administration tab page delete records from all the tables but again it exclude few tables e.g. ci_indexes, wi_fault and few other tables, the only oracle sql script is the best script or customize script is the best option to purge the data from orabpel schema.

Note: Above testing I have done on SoaSuite10g environment, database version was oracle10.0.2.4 and SOASuite10.1.3.5.1

Purging/Delete operation is very critical operation, so before doing it please make sure DBA has taken the backup of Database, so that if required he/she can restore data back.