Oracle, Oracle SOA Suite October 18, 2010 15

Delete large numbers of instances in Oracle SOA Suite 11g

Purging composites instances in Oracle SOA SUITE 11g

There are several ways to purge large amounts of instances in SOA Suite 11g database – dehydration storage;

  1. By dropping the SOA Suite repository and next, to recreate it using RCU creation utility providing exactly the same credentials.
  2. By executing a set of existing database procedures delivered by Oracle (fixed in SOA Suite 11.1.1.3.0 PS2).
  3. By truncating several tables in BPEL SOAINFRA schema.
  4. Perform a maintenance to free up some space in a database storage

1. Dropping the SOA Suite repository using RCU is straight forward. Just run the RCU utility select “Drop” instead of “Create” and follow the installer. Drop only SOAINFRA schema.

rcu_install2a

2. Examples of procedures in SOAINFRA schema that can be called to clean up dehydration storage database:

CREATE OR REPLACE PROCEDURE DELETE_OLD_INSTANCES
AS
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
MAX_INSTANCES          NUMBER;
DELETED_INSTANCES      NUMBER;
PURGE_PARTITIONED_DATA BOOLEAN := TRUE;
DAY_OFFSET             NUMBER;
BEGIN
FILTER.COMPOSITE_PARTITION_NAME:='default';
FILTER.COMPOSITE_NAME := 'My_Composite_Name';
FILTER.COMPOSITE_REVISION := '1.00.01';

DAY_OFFSET                     := 5;
FILTER.MIN_CREATED_DATE := TO_TIMESTAMP('2010-01-01','YYYY-MM-DD');
FILTER.MAX_CREATED_DATE        := SYSDATE - DAY_OFFSET;
MAX_INSTANCES                  := 50000;

DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA
);

END DELETE_OLD_INSTANCES;

 

Example of procedures 2 – purging instances of multiple composites using STATE filters.

CREATE OR REPLACE PROCEDURE DELETE_OLD_INSTANCES
AS
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
MAX_INSTANCES          NUMBER;
DELETED_INSTANCES      NUMBER;
PURGE_PARTITIONED_DATA BOOLEAN := TRUE;
DAY_OFFSET             NUMBER;
BEGIN
FILTER.COMPOSITE_PARTITION_NAME:='default';
FILTER.COMPOSITE_NAME := 'My_Composite_Name';
FILTER.COMPOSITE_REVISION := '1.00.01';

--USE either ONLY ONE filter per run;

FILTER.STATE := fabric.STATE_COMPLETED_SUCCESSFULLY;
--FILTER.STATE := fabric.STATE_RECOVERY_REQUIRED;
--FILTER.STATE := fabric.STATE_COMPLETED_SUCCESSFULLY;
--FILTER.STATE := fabric.STATE_FAULTED;
--FILTER.STATE := fabric.STATE_TERMINATED_BY_USER;
--FILTER.STATE := fabric.STATE_SUSPENDED;
--FILTER.STATE := fabric.STATE_STALE;
--FILTER.STATE := fabric.STATE_UNKNOWN;

DAY_OFFSET                     := 5;
FILTER.MIN_CREATED_DATE := TO_TIMESTAMP('2010-01-01','YYYY-MM-DD');
FILTER.MAX_CREATED_DATE        := SYSDATE - DAY_OFFSET;
MAX_INSTANCES                  := 50000;

DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA
);

END DELETE_OLD_INSTANCES;

Example of procedures 3 – purging instances filtered by name of the composites.

CREATE OR REPLACE
PROCEDURE DELETE_OLD_INSTANCES
AS
TYPE PROCESS_ARRAY_TYPE IS VARRAY(2) OF VARCHAR(50);
  FILTER INSTANCE_FILTER := INSTANCE_FILTER();
  PROCESS_ARRAY PROCESS_ARRAY_TYPE;
  MAX_INSTANCES          NUMBER;
  DELETED_INSTANCES      NUMBER;
  PURGE_PARTITIONED_DATA BOOLEAN := TRUE;
  DAY_OFFSET             NUMBER;
BEGIN
  DBMS_OUTPUT.put_line('----- BEGIN '|| SYSDATE ||'----------------'); 
  FILTER.COMPOSITE_PARTITION_NAME:='default';
  PROCESS_ARRAY           := PROCESS_ARRAY_TYPE('My_Composite_1',
						'My_Composite_2',
						'My_Composite_n');
  DAY_OFFSET              := 7;
  FILTER.MIN_CREATED_DATE := TO_TIMESTAMP('2010-01-01','YYYY-MM-DD');
  FILTER.MAX_CREATED_DATE := SYSDATE - DAY_OFFSET;
  MAX_INSTANCES           := 10000;

  FOR i  IN PROCESS_ARRAY.FIRST..PROCESS_ARRAY.LAST
  LOOP
  FILTER.COMPOSITE_NAME := PROCESS_ARRAY(i);
    DBMS_OUTPUT.put_line(' FILTER.COMPOSITE_NAME '|| FILTER.COMPOSITE_NAME); 
    DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES( 
	FILTER => FILTER, 
	MAX_INSTANCES => MAX_INSTANCES, 
	PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA);
    DBMS_OUTPUT.put_line('DELETED_INSTANCES: ' || DELETED_INSTANCES);   
  END LOOP;
  DBMS_OUTPUT.put_line('--------- BEGIN '|| SYSDATE ||'---------'); 
  END DELETE_OLD_INSTANCES;

 

3. There is also a way to truncate several tables in BPEL SOAINFRA schema and delete XML_DOCUMENTS table.

This approach is not recommended for the production environments, but in Dev/Test will do the job in a very fast way.

In SOAINFRA schema:

TRUNCATE TABLE INSTANCE_PAYLOAD DROP storage;
TRUNCATE TABLE REFERENCE_INSTANCE DROP storage;
TRUNCATE TABLE REJECTED_MSG_NATIVE_PAYLOAD DROP storage;
TRUNCATE TABLE INSTANCE_PAYLOAD DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE_FAULT DROP storage;
TRUNCATE TABLE COMPOSITE_SENSOR_VALUE DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE_ASSOC DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE DROP storage;
TRUNCATE TABLE AUDIT_TRAIL DROP storage;
TRUNCATE TABLE CUBE_INSTANCE DROP storage;
TRUNCATE TABLE CUBE_SCOPE DROP storage;
TRUNCATE TABLE AUDIT_DETAILS DROP storage;

-- DELETE OR or disable constraint and truncate XML_DOCUMENT table:
alter table "PRD_SOAINFRA"."XML_DOCUMENT" disable constraint "DOC_STORE_PK"
TRUNCATE TABLE XML_DOCUMENT;
alter table "PRD_SOAINFRA"."XML_DOCUMENT" enable constraint "DOC_STORE_PK"

DELETE FROM XML_DOCUMENT; 
COMMIT;

 

4. After all it is a good practice to do some maintenance and clean up the storage and rebuild some indexes;

ALTER TABLE XML_DOCUMENT enable row movement;
ALTER TABLE XML_DOCUMENT shrink space;
ALTER TABLE XML_DOCUMENT disable row movement;
ALTER INDEX DOC_STORE_PK rebuild online;

ALTER TABLE AUDIT_TRAIL enable row movement;
ALTER TABLE AUDIT_TRAIL shrink space;
ALTER TABLE AUDIT_TRAIL disable row movement;
ALTER INDEX AT_PK rebuild online;

ALTER TABLE HEADERS_PROPERTIES enable row movement;
ALTER TABLE HEADERS_PROPERTIES shrink space;
ALTER TABLE HEADERS_PROPERTIES disable row movement;
ALTER INDEX HEADER_PROPERTIES_PK rebuild online;

ALTER TABLE CUBE_SCOPE enable row movement;
ALTER TABLE CUBE_SCOPE shrink space;
ALTER TABLE CUBE_SCOPE disable row movement;
ALTER INDEX CS_PK 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 TABLE DLV_MESSAGE enable row movement;
ALTER TABLE DLV_MESSAGE shrink space;
ALTER TABLE DLV_MESSAGE disable row movement;
ALTER INDEX DM_CONVERSATION rebuild online;
ALTER INDEX DM_PK rebuild online;
ALTER INDEX STATE_TYPE_DATE rebuild online;

ALTER TABLE CUBE_INSTANCE enable row movement;
ALTER TABLE CUBE_INSTANCE shrink space;
ALTER TABLE CUBE_INSTANCE disable row movement;
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 TABLE INSTANCE_PAYLOAD enable row movement;
ALTER TABLE INSTANCE_PAYLOAD shrink space;
ALTER TABLE INSTANCE_PAYLOAD disable row movement;
ALTER INDEX INSTANCE_PAYLOAD_KEY rebuild online;

ALTER TABLE XML_DOCUMENT_REF enable row movement;
ALTER TABLE XML_DOCUMENT_REF shrink space;
ALTER TABLE XML_DOCUMENT_REF disable row movement;
ALTER INDEX XML_DOC_REFERENCE_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 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 INDEX DOC_DLV_MSG_GUID_INDEX rebuild online;

ALTER TABLE DLV_SUBSCRIPTION enable row movement;
ALTER TABLE DLV_SUBSCRIPTION shrink space;
ALTER TABLE DLV_SUBSCRIPTION disable row movement;
ALTER INDEX DS_CONV_STATE rebuild online;
ALTER INDEX DS_CONVERSATION rebuild online;
ALTER INDEX DS_FK rebuild online;
ALTER INDEX DS_PK rebuild online;

ALTER TABLE AUDIT_DETAILS enable row movement;
ALTER TABLE AUDIT_DETAILS shrink space;
ALTER TABLE AUDIT_DETAILS disable row movement;
ALTER INDEX AD_PK rebuild online;

ALTER TABLE WI_FAULT enable row movement;
ALTER TABLE WI_FAULT shrink space;
ALTER TABLE WI_FAULT disable row movement;
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 TABLE WORK_ITEM enable row movement;
ALTER TABLE WORK_ITEM shrink space;
ALTER TABLE WORK_ITEM disable row movement;
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;

--LOBs

ALTER TABLE XML_DOCUMENT MODIFY lob (DOCUMENT) (shrink space);
ALTER TABLE AUDIT_DETAILS MODIFY lob (bin) (shrink space);
ALTER TABLE WI_FAULT MODIFY lob (MESSAGE) (shrink space);
ALTER TABLE CUBE_SCOPE MODIFY lob (SCOPE_BIN) (shrink space);
ALTER TABLE REFERENCE_INSTANCE MODIFY lob (STACK_TRACE) (shrink space);
ALTER TABLE REFERENCE_INSTANCE MODIFY lob (ERROR_MESSAGE) (shrink space);
ALTER TABLE COMPOSITE_INSTANCE_FAULT MODIFY lob (STACK_TRACE) (shrink space);
ALTER TABLE COMPOSITE_INSTANCE_FAULT MODIFY lob (ERROR_MESSAGE) (shrink space);
ALTER TABLE TEST_DEFINITIONS MODIFY lob (DEFINITION) (shrink space);

Your comments would be much appreciated folks.

References:
http://download.oracle.com/docs/cd/E14571_01/integration.1111/e10226/soacompapp_mang.htm

Related posts:

Default ThumbnailOracle DBA Interview Questions Answered: Technical Default ThumbnailDrop multiple tables from users schemas Installing Oracle Database 12c R1 on Linux 6 with ASM Click to enlargeInstalling Oracle RAC 11gR2 on Linux 64bit
, , , ,