Friday, April 24, 2015

Oracle SOA BPM purging : My way


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.













Posted by at

1 comment:

  1. UnknownFebruary 12, 2016 at 5:53 AM

    Why are some of the alters, etc. in the last section remarked out?

    ReplyDelete

Subscribe to: Post Comments (Atom)