|ज्ञानं परमं ध्येयम्

1. Take pre-purging statistics

-- Count of all instances , all states

select
DECODE(STATE,
0,'0-Running',
1,'1-Completed',
2,'2-Running with faults',
3, '3-Completed with faults',
4, '4-Running with recovery required',
5,'5-Completed with recovery required',
6,'6-Running with faults and recovery required',
7,'7-Completed with faults and recovery required',
8,'8-Running with suspended',
9,'9-Completed with suspended',
10,'10-Running with faults and suspended',
11,'11-Completed with faults and suspended',
12,'12-Running with recovery required and suspended',
13,'13-Completed with recovery required and suspended',
14,'14-Running with faults, recovery required, and suspended',
15,'15-Completed with faults, recovery required, and suspended',
16,'16-Running with terminated',
17,'17-Completed with terminated',
18,'18-Running with faults and terminated',
19,'19-Completed with faults and terminated',
20,'20-Running with recovery required and terminated',
21,'21-Completed with recovery required and terminated',
22,'22-Running with faults, recovery required, and terminated',
23,'23-Completed with faults, recovery required, and terminated',
24,'24-Running with suspended and terminated',
25,'25-Completed with suspended and terminated',
26,'26-Running with faulted, suspended, and terminated',
27,'27-Completed with faulted, suspended, and terminated',
28,'28-Running with recovery required, suspended, and terminated',
29,'29-Completed with recovery required, suspended, and terminated',
30,'30-Running with faulted, recovery required, suspended, and terminated',
31,'31-Completed with faulted, recovery required, suspended, and terminated',
32,'32-Unknown',
34,'34-Open and Faulted',
36,'36-Running with recovery required and unknown state',
64,'64-Stale') "State",
count(1)
from composite_instance
GROUP BY STATE
ORDER BY STATE ASC;

2. List of purgeble instances

select
DECODE(STATE,
1,'1-Completed',
2,'2-Running with faults',
3, '3-Completed with faults',
4, '4-Running with recovery required',
5,'5-Completed with recovery required',
6,'6-Running with faults and recovery required',
7,'7-Completed with faults and recovery required',
8,'8-Running with suspended',
9,'9-Completed with suspended',
10,'10-Running with faults and suspended',
11,'11-Completed with faults and suspended',
12,'12-Running with recovery required and suspended',
13,'13-Completed with recovery required and suspended',
14,'14-Running with faults, recovery required, and suspended',
15,'15-Completed with faults, recovery required, and suspended',
16,'16-Running with terminated',
17,'17-Completed with terminated',
18,'18-Running with faults and terminated',
19,'19-Completed with faults and terminated',
20,'20-Running with recovery required and terminated',
21,'21-Completed with recovery required and terminated',
22,'22-Running with faults, recovery required, and terminated',
23,'23-Completed with faults, recovery required, and terminated',
24,'24-Running with suspended and terminated',
25,'25-Completed with suspended and terminated',
26,'26-Running with faulted, suspended, and terminated',
27,'27-Completed with faulted, suspended, and terminated',
28,'28-Running with recovery required, suspended, and terminated',
29,'29-Completed with recovery required, suspended, and terminated',
30,'30-Running with faulted, recovery required, suspended, and terminated',
31,'31-Completed with faulted, recovery required, suspended, and terminated',
32,'32-Unknown',
34,'34-Open and Faulted',
36,'36-Running with recovery required and unknown state',
64,'64-Stale') "State",
count(distinct ecid) as CNT
from composite_instance
where ( bitand(state,127)=1 OR bitand(state,6) =2 OR bitand(state,16) =16
OR bitand(state,64) =64 OR state between 32 and 63
OR state = 3
OR state =19)
GROUP BY STATE
ORDER BY CNT DESC;

3. Count of rows in all important tables

SELECT
(SELECT COUNT(*) FROM CUBE_INSTANCE) AS CUBE_INST,
(SELECT COUNT(*) FROM COMPOSITE_INSTANCE) AS COMP_INST,
(SELECT COUNT(*) FROM COMPONENT_INSTANCE) AS COMPNT_INST,
(SELECT COUNT(*) FROM COMPOSITE_INSTANCE_FAULT) AS COMPST_INST_FLT,
(SELECT COUNT(*) FROM REFERENCE_INSTANCE) AS REF_INST,
(SELECT COUNT(*) FROM CUBE_SCOPE) AS CUBE_SCP,
(SELECT COUNT(*) FROM COMPOSITE_SENSOR_VALUE) AS COMP_SEN_VAL,
(SELECT COUNT(*) FROM XML_DOCUMENT) AS XML_DOC,
(SELECT COUNT(*) FROM XML_DOCUMENT_REF) AS XML_DOC_REF,
(SELECT COUNT(*) FROM AUDIT_TRAIL) AS AUDIT_TRAIL,
(SELECT COUNT(*) FROM AUDIT_COUNTER) AS AUDIT_CNTR,
(SELECT COUNT(*) FROM AUDIT_DETAILS) AS AUDIT_DET,
(SELECT COUNT(*) FROM INSTANCE_PAYLOAD) AS INST_PYLD,
(SELECT COUNT(*) FROM HEADERS_PROPERTIES) AS HDRS_PROPS,
(SELECT COUNT(*) FROM WFTASK) AS WFTASK,
(SELECT COUNT(*) FROM WI_FAULT) AS WI_FLT,
(SELECT COUNT(*) FROM DLV_MESSAGE) AS DLV_MSSG,
(SELECT COUNT(*) FROM DLV_SUBSCRIPTION) AS DLV_SUBC,
(SELECT COUNT(*) FROM DOCUMENT_DLV_MSG_REF) AS DOC_DLV_MSG_REF,
(SELECT COUNT(*) FROM DOCUMENT_CI_REF) AS DOC_CI_REF,
(SELECT COUNT(*) FROM WORK_ITEM) AS WRK_ITEM
FROM DUAL;

4. Check schema free space

SELECT A1.TABLESPACE_NAME TABLE_SPACE, B.AUTOEXTENSIBLE, B.INCREMENT_BY,
SUM (A1.BYTES)/1024/1024 FREE_SPACE_MB, SUM (A1.BLOCKS) FREE_BLOCKS, SUM(B.BYTES)/1024/1024 ALLOCATED_SPACE_MB
FROM SYS.DBA_FREE_SPACE A1, SYS.DBA_DATA_FILES B
WHERE A1.TABLESPACE_NAME=B.TABLESPACE_NAME AND A1.TABLESPACE_NAME ='*_SOAINFRA'
GROUP BY A1.TABLESPACE_NAME, B.AUTOEXTENSIBLE, B.INCREMENT_BY;

5. Check table free space

select segment_name,segment_type,bytes/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TABLE' AND OWNER='*_SOAINFRA' order by 3 desc;

6. Check the database growth

SELECT * FROM(SELECT C.SEGMENT_NAME "OBJECT NAME",B.OBJECT_TYPE,SUM(SPACE_USED_DELTA) / 1024 / 1024 "GROWTH (MB)"
FROM
DBA_HIST_SNAPSHOT SN,
DBA_HIST_SEG_STAT A,
DBA_OBJECTS B,
DBA_SEGMENTS C
WHERE BEGIN_INTERVAL_TIME > TRUNC(SYSDATE)-&DAYS_BACK
AND SN.SNAP_ID = A.SNAP_ID
AND B.OBJECT_ID = A.OBJ#
AND B.OWNER = C.OWNER
AND B.OBJECT_NAME = C.SEGMENT_NAME
AND C.OWNER ='_SOAINFRA'
AND B.OBJECT_TYPE='TABLE'
GROUP BY C.TABLESPACE_NAME,C.SEGMENT_NAME,B.OBJECT_TYPE)
ORDER BY 3;

7. Run the purge script

Though we ran the purge script, we saw that most instances were not getting deleted. So i thought let me use SOA management api's to delete the instances, one by one. The below was the java code which i used to delete it.

package com.raylabs.soa.management;

import com.raylabs.soa.WriteToFile;

import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;

import javax.naming.Context;

import oracle.soa.management.facade.ComponentInstance;
import oracle.soa.management.facade.CompositeInstance;
import oracle.soa.management.facade.Locator;
import oracle.soa.management.facade.LocatorFactory;
import oracle.soa.management.util.ComponentInstanceFilter;
import oracle.soa.management.util.CompositeInstanceFilter;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;

public class DeleteInstanceUptoSomeDate {
public DeleteInstanceUptoSomeDate() {
super();
}

public static void main(String[] args) {
//Set year month and time
int year = 2015;
int month = 4;
int day = 1;
int hour =0;
int min = 0;
int sec = 00;
int maxErrorCount = 500;
boolean printStack = false;
boolean writeToFile = false;

int instanceCount = 0;
int errorCount = 0;
Locator loc = null;
long startTime = System.currentTimeMillis();
long endTime = 0;
try {
loc = LocatorFactory.createLocator(getConnectionDetails());
CompositeInstanceFilter compositeInFilter =
new CompositeInstanceFilter();
DateTime dt =
new DateTime(DateTimeZone.forTimeZone(TimeZone.getTimeZone("America/New_York"))).withDate(year,
month,
day).withTime(hour,
min,
sec,
00);
Calendar calendar = dt.toGregorianCalendar();
System.out.println("Max creation date for instance :" +
dt.toString());
compositeInFilter.setMaxCreationDate(calendar.getTime());
compositeInFilter.setPageSize(50);
compositeInFilter.setOrderBy(CompositeInstanceFilter.ORDER_BY_CREATION_DATE_ASC);
int[] states =
{ CompositeInstance.STATE_COMPLETED_SUCCESSFULLY, CompositeInstance.STATE_FAULTED,
CompositeInstance.STATE_STALE, CompositeInstance.STATE_SUSPENDED,
CompositeInstance.STATE_UNKNOWN };
compositeInFilter.setStates(states);
//System.out.println("Number of instances available for delete :"+compositeInstances.size());

do {
List<CompositeInstance> compositeInstances =
loc.getCompositeInstances(compositeInFilter);
Iterator compositeInstancesIterator =
compositeInstances.iterator();
while (compositeInstancesIterator.hasNext()) {
instanceCount++;
CompositeInstance compositeInstance =
(CompositeInstance)compositeInstancesIterator.next();
// if (writeToFile) {
// ComponentInstanceFilter componentFilter =
// new ComponentInstanceFilter();
// componentFilter.setEngineType(Locator.SE_BPEL);
// List<ComponentInstance> childComponentInstances =
// compositeInstance.getChildComponentInstances(componentFilter);
// Iterator<ComponentInstance> iterator =
// childComponentInstances.iterator();
// while (iterator.hasNext()) {
// ComponentInstance componentInstance =
// iterator.next();
// String payload =
// (String)componentInstance.getAuditTrail();
// WriteToFile.writeToFile(compositeInstance.getTitle());
// WriteToFile.writeToFile(payload);
// }
// }

System.out.print("Deleting instance with id :" +
compositeInstance.getId());
try {
System.out.print("\tComposite State :"+getCompositeState(compositeInstance.getState())+"\t Creation date: "+compositeInstance.getCreationDate());
compositeInstance.delete();
} catch (Exception e) {
instanceCount--;
System.out.print("\tError while deleting " +
"\t"+compositeInstance.getCompositeDN().getCompositeName()+"\t"+compositeInstance.getCompositeDN().getRevision());

if (printStack) {
e.printStackTrace();
}
errorCount++;
if (errorCount > maxErrorCount) {
System.out.println("\nNumber of instance deleted :" +
instanceCount);
System.out.println("Halting purging as error count has reached Max error count :" +
maxErrorCount);
System.out.println("Try deleting instances from EM console");
endTime = System.currentTimeMillis();
System.out.println("Total time taken :" +
(endTime - startTime) / 60 /60+ " sec");
System.exit(1);
}
}
System.out.println();
}

} while (loc.getCompositeInstances(compositeInFilter).size() > 0);

System.out.println("\nNumber of instance deleted :" + instanceCount);
endTime = System.currentTimeMillis();
System.out.println("Total time taken :" +
(endTime - startTime) / 60 /60+ " sec");

} catch (Exception e) {
e.printStackTrace();
} finally {
loc.close();
}

}

private static Hashtable getConnectionDetails() {
Hashtable jndiProps = new Hashtable();
jndiProps.put(Context.PROVIDER_URL,
"t3://xas:1522/soa-infra");
jndiProps.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
jndiProps.put(Context.SECURITY_PRINCIPAL, "xxx");
jndiProps.put(Context.SECURITY_CREDENTIALS, "yyy");
jndiProps.put("dedicated.connection", "true");
return jndiProps;
}

private static String getCompositeState(long state) {
String compositeState = "";
if (state == CompositeInstance.STATE_COMPLETED_SUCCESSFULLY) {
compositeState = "COMPLETED_SUCCESSFULLY";
} else if (state == CompositeInstance.STATE_FAULTED) {
compositeState = "STATE_FAULTED";
} else if (state == CompositeInstance.STATE_RUNNING) {
compositeState = "STATE_RUNNING";
} else if (state == CompositeInstance.STATE_STALE) {
compositeState = "STATE_STALE";
} else if (state == CompositeInstance.STATE_RECOVERY_REQUIRED) {
compositeState = "STATE_RECOVERY_REQUIRED";
} else if (state == CompositeInstance.STATE_SUSPENDED) {
compositeState = "STATE_SUSPENDED";
} else if (state == CompositeInstance.STATE_TERMINATED_BY_USER) {
compositeState = "STATE_SUSPENDED";
} else if (state == CompositeInstance.STATE_UNKNOWN) {
compositeState = "STATE_UNKNOWN";
} else {
compositeState = "\tUndefined";
System.out.print("\t"+state);
}
return compositeState;
}
}

After running the above script reduced the number of instances in many of the tables, but BPM_AUDIT_QUERY and MEDIATOR_INSTANCE table size and count was high and found out that the instances were not at all deleted. So i decided to delete it throught a SQL script.

DECLARE
V_START_DATE TIMESTAMP:=TO_TIMESTAMP('2014-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
V_END_DATE TIMESTAMP:=TO_TIMESTAMP('2014-09-30 23:59:00','YYYY-MM-DD HH24:MI:SS');
-- SET V_DELETE_INSTANCE TO 1 IF YOU WANT TO GET JUST THE COUNT AND 0 TO DELETE
V_DELETE_INSTANCE INTEGER:=0;
V_INSTANCE_ID INTEGER;
V_INSTANCE_COUNT INTEGER;
V_DELETED_INSTANCE_COUNT INTEGER;
V_TOTAL_INSTANCE INTEGER;
CURSOR instance_id
IS
SELECT *
FROM BPM_AUDIT_QUERY
WHERE CREATE_TIME BETWEEN V_START_DATE AND V_END_DATE
ORDER BY CREATE_TIME;
BEGIN
V_DELETED_INSTANCE_COUNT:=0;
SELECT COUNT(1)
INTO V_TOTAL_INSTANCE
FROM BPM_AUDIT_QUERY
WHERE CREATE_TIME BETWEEN V_START_DATE AND V_END_DATE
ORDER BY CREATE_TIME;
DBMS_OUTPUT.PUT_LINE('TOTAL INSTANCES :'||V_TOTAL_INSTANCE);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
IF(V_DELETE_INSTANCE =0) THEN
IF (V_TOTAL_INSTANCE!=0) THEN
FOR i IN INSTANCE_ID
LOOP
V_INSTANCE_ID:=I.COMPOSITE_INSTANCE_ID;
DBMS_OUTPUT.PUT_LINE('INSTANCE ID :'||V_INSTANCE_ID);
SELECT COUNT(1)
INTO V_INSTANCE_COUNT
FROM COMPOSITE_INSTANCE
WHERE ID=V_INSTANCE_ID;
DBMS_OUTPUT.PUT_LINE('COUNT IN COMPOSITE_INSTANCE '||V_INSTANCE_COUNT);
IF (V_INSTANCE_COUNT=0) THEN
DBMS_OUTPUT.PUT_LINE('DELETED.. ');
V_DELETED_INSTANCE_COUNT:=V_DELETED_INSTANCE_COUNT+1;
DELETE FROM BPM_AUDIT_QUERY WHERE COMPOSITE_INSTANCE_ID=V_INSTANCE_ID;
ELSE
DBMS_OUTPUT.PUT_LINE('NOT DELETED');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('NUMBER OF INSTANCES DELETED '||V_DELETED_INSTANCE_COUNT);
END IF;
END IF;
END;
/

DECLARE
-- SET V_DELETE_INSTANCE TO 1 IF YOU WANT TO GET JUST THE COUNT AND 0 TO DELETE
V_ROWNUM INTEGER:=10000;
V_INSTANCE_ID VARCHAR2(1000);
V_INSTANCE_COUNT INTEGER;
V_DELETED_INSTANCE_COUNT INTEGER;
V_TOTAL_MI_INSTANCE INTEGER;
V_TOTAL_MAD_INSTANCE INTEGER;
CURSOR instance_id
IS
SELECT * FROM MEDIATOR_AUDIT_DOCUMENT WHERE ROWNUM<V_ROWNUM;
BEGIN
SELECT COUNT(1) INTO V_TOTAL_MI_INSTANCE FROM MEDIATOR_INSTANCE;
SELECT COUNT(1) INTO V_TOTAL_MAD_INSTANCE FROM MEDIATOR_AUDIT_DOCUMENT;
DBMS_OUTPUT.PUT_LINE('NUMBER OF INSTANCES IN MEDIATOR INSTANCE :'||V_TOTAL_MI_INSTANCE);
DBMS_OUTPUT.PUT_LINE('NUMBER OF INSTANCES IN MEDIATOR_AUDIT_DOCUMENT :'||V_TOTAL_MAD_INSTANCE);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
V_DELETED_INSTANCE_COUNT:=0;
FOR i IN INSTANCE_ID
LOOP
V_INSTANCE_ID:=I.INSTANCE_ID;
--DBMS_OUTPUT.PUT_LINE('INSTANCE ID :'||V_INSTANCE_ID);
SELECT COUNT(1)
INTO V_INSTANCE_COUNT
FROM MEDIATOR_INSTANCE
WHERE ID=V_INSTANCE_ID;
--DBMS_OUTPUT.PUT_LINE('COUNT IN COMPOSITE_INSTANCE '||V_INSTANCE_COUNT);
IF (V_INSTANCE_COUNT=0) THEN
--DBMS_OUTPUT.PUT_LINE('DELETED.. ');
V_DELETED_INSTANCE_COUNT:=V_DELETED_INSTANCE_COUNT+1;
DELETE FROM MEDIATOR_AUDIT_DOCUMENT WHERE INSTANCE_ID=V_INSTANCE_ID;
-- ELSE
--DBMS_OUTPUT.PUT_LINE('NOT DELETED');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('NUMBER OF INSTANCES DELETED '||V_DELETED_INSTANCE_COUNT);
SELECT COUNT(1) INTO V_TOTAL_MI_INSTANCE FROM MEDIATOR_INSTANCE;
DBMS_OUTPUT.PUT_LINE('NUMBER OF INSTANCES IN MEDIATOR INSTANCE :'||V_TOTAL_MI_INSTANCE);
SELECT COUNT(1) INTO V_TOTAL_MAD_INSTANCE FROM MEDIATOR_AUDIT_DOCUMENT;
DBMS_OUTPUT.PUT_LINE('NUMBER OF INSTANCES IN MEDIATOR_AUDIT_DOCUMENT :'||V_TOTAL_MAD_INSTANCE);
COMMIT;
END;
/

After deleting the rows i ran the below queries

ALTER TABLE AUDIT_COUNTER ENABLE ROW MOVEMENT;
ALTER TABLE AUDIT_COUNTER SHRINK SPACE;
ALTER TABLE AUDIT_COUNTER 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 AUDIT_TRAIL ENABLE ROW MOVEMENT;
ALTER TABLE AUDIT_TRAIL SHRINK SPACE;
ALTER TABLE AUDIT_TRAIL 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_CUBE_ACTIVITY ENABLE ROW MOVEMENT;
ALTER TABLE BPM_CUBE_ACTIVITY SHRINK SPACE;
ALTER TABLE BPM_CUBE_ACTIVITY DISABLE ROW MOVEMENT;

ALTER TABLE BPM_CUBE_ACTIVITY_SEQFLOW ENABLE ROW MOVEMENT;
ALTER TABLE BPM_CUBE_ACTIVITY_SEQFLOW SHRINK SPACE;
ALTER TABLE BPM_CUBE_ACTIVITY_SEQFLOW 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_PROCESS ENABLE ROW MOVEMENT;
ALTER TABLE BPM_CUBE_PROCESS SHRINK SPACE;
ALTER TABLE BPM_CUBE_PROCESS 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 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_WORKLOAD ENABLE ROW MOVEMENT;
ALTER TABLE BPM_CUBE_WORKLOAD SHRINK SPACE;
ALTER TABLE BPM_CUBE_WORKLOAD 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 COMPONENT_INSTANCE ENABLE ROW MOVEMENT;
ALTER TABLE COMPONENT_INSTANCE SHRINK SPACE;
ALTER TABLE COMPONENT_INSTANCE DISABLE ROW MOVEMENT;

--ALTER TABLE COMPOSITE_INSTANCE_FAULTS ENABLE ROW MOVEMENT;
--ALTER TABLE COMPOSITE_INSTANCE SHRINK_FAULTS SPACE;
--ALTER TABLE COMPOSITE_INSTANCE_FAULTS 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 CUBE_INSTANCE ENABLE ROW MOVEMENT;
ALTER TABLE CUBE_INSTANCE SHRINK SPACE;
ALTER TABLE CUBE_INSTANCE 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 DLV_MESSAGE ENABLE ROW MOVEMENT;
ALTER TABLE DLV_MESSAGE SHRINK SPACE;
ALTER TABLE DLV_MESSAGE 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 DOCUMENT_CI_REF ENABLE ROW MOVEMENT;
ALTER TABLE DOCUMENT_CI_REF SHRINK SPACE;
ALTER TABLE DOCUMENT_CI_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 HEADERS_PROPERTIES ENABLE ROW MOVEMENT;
ALTER TABLE HEADERS_PROPERTIES SHRINK SPACE;
ALTER TABLE HEADERS_PROPERTIES 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 MEDIATOR_AUDIT_DOCUMENT ENABLE ROW MOVEMENT;
ALTER TABLE MEDIATOR_AUDIT_DOCUMENT SHRINK SPACE;
ALTER TABLE MEDIATOR_AUDIT_DOCUMENT 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_CASE_INSTANCE ENABLE ROW MOVEMENT;
ALTER TABLE MEDIATOR_CASE_INSTANCE SHRINK SPACE;
ALTER TABLE MEDIATOR_CASE_INSTANCE 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_GROUP_STATUS ENABLE ROW MOVEMENT;
ALTER TABLE MEDIATOR_GROUP_STATUS SHRINK SPACE;
ALTER TABLE MEDIATOR_GROUP_STATUS DISABLE ROW MOVEMENT;

--GOT ERROR WHILE RUNNING SO COMMENTING ORA-10631: SHRINK CLAUSE SHOULD NOT BE SPECIFIED FOR THIS OBJECT
--ALTER TABLE MEDIATOR_INSTANCE ENABLE ROW MOVEMENT;
--ALTER TABLE MEDIATOR_INSTANCE SHRINK SPACE;
--ALTER TABLE MEDIATOR_INSTANCE DISABLE ROW MOVEMENT;

--ALTER TABLE REFERENCE_INSTANCE ENABLE ROW MOVEMENT;
--ALTER TABLE REFERENCE_INSTANCE SHRINK SPACE;
--ALTER TABLE REFERENCE_INSTANCE DISABLE ROW MOVEMENT;

-- ALTER TABLE REJECTED_MESSAGE ENABLE ROW MOVEMENT;
-- ALTER TABLE REJECTED_MESSAGE SHRINK SPACE;
-- ALTER TABLE REJECTED_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 WFASSIGNEE ENABLE ROW MOVEMENT;
ALTER TABLE WFASSIGNEE SHRINK SPACE;
ALTER TABLE WFASSIGNEE DISABLE ROW MOVEMENT;

ALTER TABLE WFATTRIBUTELABELMAP ENABLE ROW MOVEMENT;
ALTER TABLE WFATTRIBUTELABELMAP SHRINK SPACE;
ALTER TABLE WFATTRIBUTELABELMAP DISABLE ROW MOVEMENT;

ALTER TABLE WFATTRIBUTELABELUSAGE ENABLE ROW MOVEMENT;
ALTER TABLE WFATTRIBUTELABELUSAGE SHRINK SPACE;
ALTER TABLE WFATTRIBUTELABELUSAGE DISABLE ROW MOVEMENT;

ALTER TABLE WFHEADERPROPS ENABLE ROW MOVEMENT;
ALTER TABLE WFHEADERPROPS SHRINK SPACE;
ALTER TABLE WFHEADERPROPS DISABLE ROW MOVEMENT;

ALTER TABLE WFMESSAGEATTRIBUTE ENABLE ROW MOVEMENT;
ALTER TABLE WFMESSAGEATTRIBUTE SHRINK SPACE;
ALTER TABLE WFMESSAGEATTRIBUTE DISABLE ROW MOVEMENT;

ALTER TABLE WFROUTINGSLIP ENABLE ROW MOVEMENT;
ALTER TABLE WFROUTINGSLIP SHRINK SPACE;
ALTER TABLE WFROUTINGSLIP DISABLE ROW MOVEMENT;

ALTER TABLE WFTASK ENABLE ROW MOVEMENT;
ALTER TABLE WFTASK SHRINK SPACE;
ALTER TABLE WFTASK DISABLE ROW MOVEMENT;

ALTER TABLE WFTASKAGGREGATION ENABLE ROW MOVEMENT;
ALTER TABLE WFTASKAGGREGATION SHRINK SPACE;
ALTER TABLE WFTASKAGGREGATION DISABLE ROW MOVEMENT;

ALTER TABLE WFTASKASSIGNMENTSTATISTIC ENABLE ROW MOVEMENT;
ALTER TABLE WFTASKASSIGNMENTSTATISTIC SHRINK SPACE;
ALTER TABLE WFTASKASSIGNMENTSTATISTIC 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 WFTASKMETADATA ENABLE ROW MOVEMENT;
ALTER TABLE WFTASKMETADATA SHRINK SPACE;
ALTER TABLE WFTASKMETADATA 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 WFUSERTASKVIEW ENABLE ROW MOVEMENT;
ALTER TABLE WFUSERTASKVIEW SHRINK SPACE;
ALTER TABLE WFUSERTASKVIEW 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 WLI_QS_REPORT_ATTRIBUTE ENABLE ROW MOVEMENT;
ALTER TABLE WLI_QS_REPORT_ATTRIBUTE SHRINK SPACE;
ALTER TABLE WLI_QS_REPORT_ATTRIBUTE DISABLE ROW MOVEMENT;

ALTER TABLE WLI_QS_REPORT_DATA ENABLE ROW MOVEMENT;
ALTER TABLE WLI_QS_REPORT_DATA SHRINK SPACE;
ALTER TABLE WLI_QS_REPORT_DATA 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 XML_DOCUMENT ENABLE ROW MOVEMENT;
ALTER TABLE XML_DOCUMENT SHRINK SPACE;
ALTER TABLE XML_DOCUMENT 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;

-- FOR BLOB

ALTER TABLE AUDIT_DETAILS MODIFY LOB (BIN) (SHRINK SPACE);
ALTER TABLE BPM_AUDIT_QUERY MODIFY LOB (AUDIT_COMMENT) (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);
ALTER TABLE MEDIATOR_AUDIT_DOCUMENT MODIFY LOB (DOCUMENT) (SHRINK SPACE);

-- REBUILD INDEXES

ALTER INDEX AC_PK REBUILD ONLINE; -- AUDIT_COUNTER

ALTER INDEX AD_PK REBUILD ONLINE; -- AUDIT_DETAILS

ALTER INDEX AT_PK REBUILD ONLINE; -- AUDIT_TRAIL

ALTER INDEX BPM_AUDIT_QUERY_INDEX4 REBUILD ONLINE; -- BPM_AUDIT_QUERY
ALTER INDEX BPM_AUDIT_QUERY_INDEX5 REBUILD ONLINE; -- BPM_AUDIT_QUERY
ALTER INDEX BPM_AUDIT_QUERY_INDEX3 REBUILD ONLINE; -- BPM_AUDIT_QUERY
ALTER INDEX BPM_AUDIT_QUERY_PK REBUILD ONLINE; -- BPM_AUDIT_QUERY
ALTER INDEX BPM_AUDIT_QUERY_INDEX1 REBUILD ONLINE; -- BPM_AUDIT_QUERY
ALTER INDEX BPM_AUDIT_QUERY_INDEX2 REBUILD ONLINE; -- BPM_AUDIT_QUERY

ALTER INDEX BPM_CUBE_ACTIVITY_PK REBUILD ONLINE; -- BPM_CUBE_ACTIVITY
ALTER INDEX BPM_CUBE_ACTIVITY_INDX1 REBUILD ONLINE; -- BPM_CUBE_ACTIVITY

ALTER INDEX BPM_CUBE_ACTIVITY_SEQFLOW_PK REBUILD ONLINE; -- BPM_CUBE_ACTIVITY_SEQFLOW

ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX1 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX8 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX7 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX6 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX5 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_PK REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX2 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX3 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE
ALTER INDEX BPM_CUBE_AUDITINSTANCE_INDX4 REBUILD ONLINE; -- BPM_CUBE_AUDITINSTANCE

ALTER INDEX BPM_CUBE_PROCESS_INDX2 REBUILD ONLINE; -- BPM_CUBE_PROCESS
ALTER INDEX BPM_CUBE_PROCESS_INDX1 REBUILD ONLINE; -- BPM_CUBE_PROCESS
ALTER INDEX BPM_CUBE_PROCESS_PK REBUILD ONLINE; -- BPM_CUBE_PROCESS
ALTER INDEX BPM_CUBE_PROCESS_INDX3 REBUILD ONLINE; -- BPM_CUBE_PROCESS

ALTER INDEX BPM_CUBE_PROCESSPERFORMANCE_PK REBUILD ONLINE; -- BPM_CUBE_PROCESSPERFORMANCE
ALTER INDEX BPM_CUBE_PROCPERF_INDX1 REBUILD ONLINE; -- BPM_CUBE_PROCESSPERFORMANCE
ALTER INDEX BPM_CUBE_PROCPERF_INDX2 REBUILD ONLINE; -- BPM_CUBE_PROCESSPERFORMANCE
ALTER INDEX BPM_CUBE_PROCPERF_INDX3 REBUILD ONLINE; -- BPM_CUBE_PROCESSPERFORMANCE

ALTER INDEX BPM_CUBE_TASKPERFORMANCE_PK REBUILD ONLINE; -- BPM_CUBE_TASKPERFORMANCE
ALTER INDEX BPM_CUBE_TASKPERF_INDX1 REBUILD ONLINE; -- BPM_CUBE_TASKPERFORMANCE
ALTER INDEX BPM_CUBE_TASKPERF_INDX2 REBUILD ONLINE; -- BPM_CUBE_TASKPERFORMANCE
ALTER INDEX BPM_CUBE_TASKPERF_INDX3 REBUILD ONLINE; -- BPM_CUBE_TASKPERFORMANCE

ALTER INDEX BPM_CUBE_WORKLOAD_PK REBUILD ONLINE; -- BPM_CUBE_WORKLOAD
ALTER INDEX BPM_CUBE_WORKLOAD_INDX1 REBUILD ONLINE; -- BPM_CUBE_WORKLOAD

ALTER INDEX BPM_MEASUREMENT_ACTIONS_PK REBUILD ONLINE; -- BPM_MEASUREMENT_ACTIONS
ALTER INDEX BPM_MEASUREMENT_ACTIONS_N1 REBUILD ONLINE; -- BPM_MEASUREMENT_ACTIONS
ALTER INDEX BPM_MEASUREMENT_ACTIONS_U1 REBUILD ONLINE; -- BPM_MEASUREMENT_ACTIONS
ALTER INDEX BPM_MEASURE_ACTIONS_N2 REBUILD ONLINE; -- BPM_MEASUREMENT_ACTIONS
ALTER INDEX BPM_MEASURE_ACTIONS_N3 REBUILD ONLINE; -- BPM_MEASUREMENT_ACTIONS

ALTER INDEX COMPONENT_INSTANCE_ID REBUILD ONLINE; -- COMPONENT_INSTANCE
ALTER INDEX COMPONENT_INSTANCE_ECID REBUILD ONLINE; -- COMPONENT_INSTANCE

-- ALTER INDEX COMPOSITE_INSTANCE_CO_ID REBUILD ONLINE; -- COMPOSITE_INSTANCE
-- ALTER INDEX COMPOSITE_INSTANCE_CREATED REBUILD ONLINE; -- COMPOSITE_INSTANCE
-- ALTER INDEX COMPOSITE_INSTANCE_STATE REBUILD ONLINE; -- COMPOSITE_INSTANCE
-- ALTER INDEX COMPOSITE_INSTANCE_CIDN REBUILD ONLINE; -- COMPOSITE_INSTANCE
-- ALTER INDEX COMPOSITE_INSTANCE_ID REBUILD ONLINE; -- COMPOSITE_INSTANCE
-- ALTER INDEX COMPOSITE_INSTANCE_ECID REBUILD ONLINE; -- COMPOSITE_INSTANCE

ALTER INDEX COMPOSITE_SENSOR_VALUE_IDX REBUILD ONLINE; -- COMPOSITE_SENSOR_VALUE

ALTER INDEX CI_NAME_REV_STATE REBUILD ONLINE; -- CUBE_INSTANCE
ALTER INDEX CI_ECID REBUILD ONLINE; -- CUBE_INSTANCE
ALTER INDEX CI_PK REBUILD ONLINE; -- CUBE_INSTANCE
ALTER INDEX CI_CUSTOM3 REBUILD ONLINE; -- CUBE_INSTANCE
ALTER INDEX CI_CREATION_DATE REBUILD ONLINE; -- CUBE_INSTANCE

ALTER INDEX CS_PK REBUILD ONLINE; -- CUBE_SCOPE

ALTER INDEX CONV_ID_STATE_DLV_TYPE REBUILD ONLINE; -- DLV_MESSAGE
ALTER INDEX DM_CONVERSATION REBUILD ONLINE; -- DLV_MESSAGE
ALTER INDEX STATE_TYPE_CIKEY REBUILD ONLINE; -- DLV_MESSAGE
ALTER INDEX DLV_MESSAGE_CIKEY REBUILD ONLINE; -- DLV_MESSAGE
ALTER INDEX DLV_MESSAGE_DATE_ECID REBUILD ONLINE; -- DLV_MESSAGE
ALTER INDEX DLV_MESSAGE_PK REBUILD ONLINE; -- DLV_MESSAGE

ALTER INDEX DS_CONV_STATE REBUILD ONLINE; -- DLV_SUBSCRIPTION
ALTER INDEX DS_FK REBUILD ONLINE; -- DLV_SUBSCRIPTION
ALTER INDEX DLV_SUBSCRIPTION_PK REBUILD ONLINE; -- DLV_SUBSCRIPTION
ALTER INDEX DS_CONVERSATION REBUILD ONLINE; -- DLV_SUBSCRIPTION

ALTER INDEX DOCUMENT_CI_REF_PK REBUILD ONLINE; -- DOCUMENT_CI_REF

ALTER INDEX DOC_DLV_MSG_ID_INDEX REBUILD ONLINE; -- DOCUMENT_DLV_MSG_REF
ALTER INDEX DOC_DLV_MSG_GUID_INDEX REBUILD ONLINE; -- DOCUMENT_DLV_MSG_REF

ALTER INDEX HEADERS_PROPERTIES_PK REBUILD ONLINE; -- HEADERS_PROPERTIES

ALTER INDEX INSTANCE_PAYLOAD_KEY REBUILD ONLINE; -- INSTANCE_PAYLOAD

ALTER INDEX MEDIATOR_AUDIT_DOCUMENT_PK REBUILD ONLINE; -- MEDIATOR_AUDIT_DOCUMENT

ALTER INDEX MEDIATOR_CASE_DETAIL_INDEX1 REBUILD ONLINE; -- MEDIATOR_CASE_DETAIL
ALTER INDEX MEDIATOR_CASE_DETAIL_INDEX2 REBUILD ONLINE; -- MEDIATOR_CASE_DETAIL

ALTER INDEX MEDIATOR_CASE_INSTANCE_INDEX2 REBUILD ONLINE; -- MEDIATOR_CASE_INSTANCE
ALTER INDEX MEDIATOR_CASE_INSTANCE_PK REBUILD ONLINE; -- MEDIATOR_CASE_INSTANCE
ALTER INDEX MEDIATOR_CASE_INSTANCE_INDEX3 REBUILD ONLINE; -- MEDIATOR_CASE_INSTANCE

ALTER INDEX MEDIATOR_CORRELATION_INDEX1 REBUILD ONLINE; -- MEDIATOR_CORRELATION

ALTER INDEX MEDIATOR_GROUP_STATUS_INDEX1 REBUILD ONLINE; -- MEDIATOR_GROUP_STATUS

ALTER INDEX MEDIATOR_INSTANCE_INDEX1 REBUILD ONLINE; -- MEDIATOR_INSTANCE
ALTER INDEX MEDIATOR_INSTANCE_INDEX2 REBUILD ONLINE; -- MEDIATOR_INSTANCE
ALTER INDEX MEDIATOR_INSTANCE_INDEX3 REBUILD ONLINE; -- MEDIATOR_INSTANCE
ALTER INDEX MEDIATOR_INSTANCE_INDEX4 REBUILD ONLINE; -- MEDIATOR_INSTANCE
ALTER INDEX MEDIATOR_INSTANCE_INDEX5 REBUILD ONLINE; -- MEDIATOR_INSTANCE
ALTER INDEX MEDIATOR_INSTANCE_INDEX6 REBUILD ONLINE; -- MEDIATOR_INSTANCE

-- ALTER INDEX REFERENCE_INSTANCE_ECID REBUILD ONLINE; -- REFERENCE_INSTANCE
-- ALTER INDEX REFERENCE_INSTANCE_ID REBUILD ONLINE; -- REFERENCE_INSTANCE
-- ALTER INDEX REFERENCE_INSTANCE_CO_ID REBUILD ONLINE; -- REFERENCE_INSTANCE
-- ALTER INDEX REFERENCE_INSTANCE_CDN_STATE REBUILD ONLINE; -- REFERENCE_INSTANCE
-- ALTER INDEX REFERENCE_INSTANCE_STATE REBUILD ONLINE; -- REFERENCE_INSTANCE
-- ALTER INDEX REFERENCE_INSTANCE_TIME_CDN REBUILD ONLINE; -- REFERENCE_INSTANCE

-- ALTER INDEX REJECTED_MESSAGE_ECID REBUILD ONLINE; -- REJECTED_MESSAGE
-- ALTER INDEX REJECTED_MESSAGE_CIDN REBUILD ONLINE; -- REJECTED_MESSAGE
-- ALTER INDEX REJECTED_MESSAGE_EC_CT REBUILD ONLINE; -- REJECTED_MESSAGE
-- ALTER INDEX REJECTED_MESSAGE_CT_EC REBUILD ONLINE; -- REJECTED_MESSAGE

ALTER INDEX REJECTED_MSG_NATIVE_PAYLOAD_PK REBUILD ONLINE; -- REJECTED_MSG_NATIVE_PAYLOAD

ALTER INDEX WFASSIGNEEASSIGNEETYP_I REBUILD ONLINE; -- WFASSIGNEE
ALTER INDEX WFASSIGNEECOMPOSITEID_I REBUILD ONLINE; -- WFASSIGNEE
ALTER INDEX WFASSIGNEEASSIGNEE_I REBUILD ONLINE; -- WFASSIGNEE
ALTER INDEX WFASSIGNEE_PK REBUILD ONLINE; -- WFASSIGNEE
ALTER INDEX WFASSIGNEETASKID_I REBUILD ONLINE; -- WFASSIGNEE
ALTER INDEX WFASSIGNEETYPE_I REBUILD ONLINE; -- WFASSIGNEE

ALTER INDEX WFATTRIBUTELABELMAP_PK REBUILD ONLINE; -- WFATTRIBUTELABELMAP
ALTER INDEX WFATTRIBUTEMAP_UK REBUILD ONLINE; -- WFATTRIBUTELABELMAP

ALTER INDEX WFATTRIBUTEUSAGE_UK REBUILD ONLINE; -- WFATTRIBUTELABELUSAGE

ALTER INDEX WFHEADERPROPS_PK REBUILD ONLINE; -- WFHEADERPROPS

ALTER INDEX WFMESSAGEATTRIBUTETASKNAME_I REBUILD ONLINE; -- WFMESSAGEATTRIBUTE
ALTER INDEX WFMESSAGEATTRIBUTETASKID_I REBUILD ONLINE; -- WFMESSAGEATTRIBUTE

ALTER INDEX WFROUTINGSLIP_PK REBUILD ONLINE;--WFROUTINGSLIP

ALTER INDEX WFTASKSYSTEMID REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKPARENTCOMPONENTINST_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKOWNERUSER_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKOWNERROLE_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKOWNERGROUP_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKOASSIGNEEUSER_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKNUMBER_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKINSTANCEID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKIDSTATEGRPIC_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKIDENTIFICATIONKEY_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKEMCOUNT_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKECID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKROOTIDAGGRTASKID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKJOURNALID REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKCLAIMKEY REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKSTATE_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKSTECPTTYPIDCTXAGGIDORGID REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKSTEROOTIDTASKIDAGGRID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKTASKGROUPID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKTASKIDPATTERN_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKTASKIDTASKGROUPID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKUPDATEDBY_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKUPDDTESTEAGGRIDTMPTSK_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASK_PK REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKACQUIREDBY_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKAGGREGATIONTASKID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKCOMPONENTTYPE_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKCOMPOSITEDN_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKCOMPOSITEID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKCREATEDDATE_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKCREATOR_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKDOCPACID_I REBUILD ONLINE; -- WFTASK
ALTER INDEX WFTASKDOMAINID_I REBUILD ONLINE; -- WFTASK

ALTER INDEX WFTASKAGGREGATIONSTAGE_I REBUILD ONLINE; -- WFTASKAGGREGATION
ALTER INDEX WFTASKAGGREGATION_PK REBUILD ONLINE; -- WFTASKAGGREGATION
ALTER INDEX WFTASKAGGREGATIONAGGTYPE_I REBUILD ONLINE; -- WFTASKAGGREGATION
ALTER INDEX WFTASKAGGREGATIONCOLNAME_I REBUILD ONLINE; -- WFTASKAGGREGATION
ALTER INDEX WFTASKAGGREGATIONKEYLIST_I REBUILD ONLINE; -- WFTASKAGGREGATION
ALTER INDEX WFTASKAGGREGATIONTARGETINDEX_I REBUILD ONLINE; -- WFTASKAGGREGATION
ALTER INDEX WFTASKAGGREGATIONROOTTASKID_I REBUILD ONLINE; -- WFTASKAGGREGATION

ALTER INDEX WFTASKASASSIGNEETYPE_I REBUILD ONLINE; -- WFTASKASSIGNMENTSTATISTIC
ALTER INDEX WFTASKASSIGNMENTSTATISTIC_PK REBUILD ONLINE; -- WFTASKASSIGNMENTSTATISTIC
ALTER INDEX WFTASKASSIGNMENTSTATISTICID_I REBUILD ONLINE; -- WFTASKASSIGNMENTSTATISTIC
ALTER INDEX WFTASKASSTARTENDDATE_I REBUILD ONLINE; -- WFTASKASSIGNMENTSTATISTIC

ALTER INDEX WFTASKHUPDATEDBY_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHOASSIGNEEUSER_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHTASKIDPATTERN_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHTASKGROUPID_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHSUBTASKGRPINSID_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHSATE_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHPARENTCOMPONENTINST_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHOWNERUSER_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHOWNERROLE_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHOWNERGROUP_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHISTORY_PK REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHACQUIREDBY_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHCOMPONENTTYPE_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHCOMPOSITEDN_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHCOMPOSITEID_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHCREATOR_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHDOCPACID_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHDOMAINID_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHECID_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHGRPIDINHIST_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHIDENTIFICATIONKEY_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHIDINHIST_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHIDSTATEGRPIC_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHINSTANCEID_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHNUMBER_I REBUILD ONLINE; -- WFTASKHISTORY
ALTER INDEX WFTASKHTASKIDTASKGROUPID_I REBUILD ONLINE; -- WFTASKHISTORY

ALTER INDEX WFTASKHISTORY_TL_PK REBUILD ONLINE; -- WFTASKHISTORY_TL

ALTER INDEX WFTASKMETADATA_PK REBUILD ONLINE; -- WFTASKMETADATA
ALTER INDEX WFTASKMETADATA_NAMESPCVERIDX REBUILD ONLINE; -- WFTASKMETADATA

ALTER INDEX WFTASK_TL_PK REBUILD ONLINE; -- WFTASK_TL

ALTER INDEX WFUSERTASKVIEW_PRESID_I REBUILD ONLINE; -- WFUSERTASKVIEW
ALTER INDEX WFUSERTASKVIEW_PK REBUILD ONLINE; -- WFUSERTASKVIEW
ALTER INDEX WFUSERTASKVIEW_UIDX REBUILD ONLINE; -- WFUSERTASKVIEW

ALTER INDEX WF_FK2 REBUILD ONLINE; -- WI_FAULT
ALTER INDEX WF_CRDATE_TYPE REBUILD ONLINE; -- WI_FAULT
ALTER INDEX WF_CRDATE_CIKEY REBUILD ONLINE; -- WI_FAULT
ALTER INDEX WI_FAULT_PK REBUILD ONLINE; -- WI_FAULT

ALTER INDEX IX_WLI_QS_REPORT_ATTRIBUTE_DM REBUILD ONLINE; -- WLI_QS_REPORT_ATTRIBUTE
ALTER INDEX WLI_QS_REPORT_ATTRIBUTE_PK REBUILD ONLINE; -- WLI_QS_REPORT_ATTRIBUTE
ALTER INDEX IX_WLI_QS_REPORT_ATTRIBUTE_IED REBUILD ONLINE; -- WLI_QS_REPORT_ATTRIBUTE

ALTER INDEX IX_WLI_QS_REPORT_DATA REBUILD ONLINE; -- WLI_QS_REPORT_DATA

ALTER INDEX WORK_ITEM_PK REBUILD ONLINE; -- WORK_ITEM
ALTER INDEX WI_EXPIRED REBUILD ONLINE; -- WORK_ITEM
ALTER INDEX WI_STRANDED REBUILD ONLINE; -- WORK_ITEM
ALTER INDEX WI_STATE_KEY REBUILD ONLINE; -- WORK_ITEM

ALTER INDEX DOC_STORE_PK REBUILD ONLINE; -- XML_DOCUMENT

ALTER INDEX XML_DOCUMENT_REF_PK REBUILD ONLINE; -- XML_DOCUMENT_REF

After running the above scripts , the space and count in the tables was reduced significantly.

Note : This method is not a recommended way and has been done through trial and error method. Please take proper backup ,advice from DBA and Senior consultants before following the above method.

|ज्ञानं परमं ध्येयम् |

Tuesday, April 28, 2015

Jenkins useful posts

Friday, April 24, 2015

Oracle SOA BPM purging : My way

Thursday, April 2, 2015

Oracle SOA BPM : Task definition not found using namespace error

Followers