Tuesday, September 8, 2015

Oracle SOA BPM notes : This is the digital notes that I have taken from various sources.


  1. Composite :
  2. Component :
  3. Service :
  4. Reference :
  5. Adapters :
  6. Mediator :
  7. BPEL
  8. BPMN
  9. Spring
  10. Human task
  11. Business Rules
  12. Task Api's 
  13. Facade Api's
  14. BPM api's
  15. JMX api's
  16. XA and non XA datasource
  17. Purging ;
  18. EDN 
  19. OSB
  20. ADF
  21. BAM
  22. XML
  23. XSD
  24. WSDL
  25. XSLT
  26. WSDL
  27. Weblogic Server
  28. SOA security
  29. Instance patching
  30. BPA

Posted by at No comments:
Labels: , ,

Wednesday, August 12, 2015

OBPM INTRO

                                            OBPM

What ?

Oracle BPM is short Oracle Business Process Management, a powerful set of tools for creating, executing, and optimizing business processes that can great improve efficiency, data sharing, and customer service for your company.

Oracle BPM is part of Oracle Fusion Middleware Family built on top of very successful Oracle SOA suite 11g.

Oracle BPM is around in the market from 2009.

IBM BPM and PEGA are the main competitors.


Why?

1. The suite enables unparalleled collaboration between business and IT to automate and optimize business processes.

2. Today's businesses operate in a rapidly changing climate that demands an unprecedented level of adaptation and innovation. These conditions place equally unprecedented burdens on IT departments, which must now deliver solutions in months rather than years. And those solutions must themselves be flexible enough to support continuous adaptation to an ever-evolving, ever-expanding marketplace.

3.Today’s organizations face increasing business pressures due to globalization, changing competitors, stricter regulations, faster pace of
innovation, and demand for better customer service.

4. Trends such as mobile, social, cloud, and analytic s have a profound impact on a company’s business and its core business processes. Companies expect business driven rapid process automation, seamless collaboration among all stakeholders, deep insights for better business decisions, unprecedented agility and operational excellence.




How ?

OBPM provides standard set of tools to achieve the objectives defined earlier. 

Complete set to of tools from Modelling , Design, Development, Execution and Monitoring.

Can be used to Achieve Round trip integration of a process.

Round trip integration is used to improve process through cycles of design and development.

Agile and DEVOPS complement this naturally.

Tools  are described below 

Process composer :

  1. Primary designed for Business analyst ,Process experts and Functional experts.
  2. A Web based tool.
  3. Create , Import and Modify(limited) BPM processes.
  4. After creation of Process , publish it to the Repository, where IT developer can pick it and take over the development activity.

BPM studio :


  1. A Jdeveloper Addon. Jdeveloper is a standard tool for Application development in Oracle Fusion Middleware family.
  2. Complete development features like  Code, compile and packaging .
  3. Primarily targeted on IT developer.
  4. One stop for all  OBPM development.


Weblogic Server :


  1.  A runtime platform for the BPM process.
  2.  An excellent Enterprise application server.
  3.  Excellent load balancing and load handling capabilities.


Enterprise manager :


  1. Web application to Administrators to monitor and control all BPM processes.
  2. View each instance details and current state of all BPM process.



BPM workspace :


  1.  A web application to end users to act on task assigned via BPM process.
  2. Any task assigned to logged in user appears on his bpm workspace task list.
  3. Admin can also use to create, update and delete process roles etc.


BAM :



  1. View realtime metrics derived from the running bpm processes.
  2. Metrics has to be configured during design time.
  3. Metrics are used to modify the business process.


 BPMN : 


  1. Business Process Model and Notation (BPMN) is a graphical representation for specifying business processes in a business process model.
  2. A standard Business Process Model and Notation (BPMN) will provide businesses with the capability of understanding their internal business procedures in a graphical notation and will give organizations the ability to communicate these procedures in a standard manner.
  3. The graphical notation will facilitate the understanding of the performance collaborations and business transactions between the organizations. This will ensure that businesses will understand themselves and participants in their business and will enable organizations to adjust to new internal and B2B business circumstances quickly.
  4. OBPM suite uses BPMN 2.0




Runtime architecture







Posted by at No comments:
Labels:

Thursday, July 16, 2015

Create and Deploy Web project using Maven

1. Download below jar/zip's
a. apache-maven-3.3.3-bin
b. apache-tomcat-8.0.23

2. Set environment variables
a. CATALINA_HOME = C:\Users\raylabs\Downloads\apache-tomcat-8.0.23\apache-tomcat-8.0.23
b.M2_HOME = C:\Users\raylabs\Downloads\apache-maven-3.3.3-bin\apache-maven-3.3.3
c : PATH =C:\Program Files\Java\jdk1.8.0_45\bin;C:\Users\raylabs\Downloads\apache-maven-3.3.3-bin\apache-maven-3.3.3\bin;%CATALINA_HOME%\bin
d. MAVEN_HOME =C:\Users\raylabs\Downloads\apache-maven-3.3.3-bin\apache-maven-3.3.3


3. Create Workspace folder :
D:\projects\jsf

4. Open command prompt and enter the command :  mvn -version

[RAYLABS.D-4654646] ➤ mvn -version
Apache Maven 3.3.3 (7994120775791599e205a5524ec3e0dfe41d4a06; 2015-04-22T17:27:37+05:30)
Maven home: C:\Users\raylabs\Downloads\apache-maven-3.3.3-bin\apache-maven-3.3.3
Java version: 1.8.0_45, vendor: Oracle Corporation
Java home: C:\Program Files\Java\jdk1.8.0_45\jre
Default locale: en_US, platform encoding: Cp1252
OS name: "windows 7", version: "6.1", arch: "x86", family: "dos"


5. Create a project using maven script command :

mvn archetype:generate -DgroupId=com.raylabs.maven -DartifactId=HelloMaven -DarchetypeArtifactId=maven-archetype-webapp -DinteractiveMode=false


6. Create eclipse project with the same : 

a. Enter the project folder (HelloMaven)
b. Run the command : mvn eclipse:eclipse

Eclipse .project will be created and can be imported


7. Add user to tomcat-user file 
C:\Users\raylabs\Downloads\apache-tomcat-8.0.23\apache-tomcat-8.0.23\conf\tomcat-users.xml

<role rolename="manager"/>
<role rolename="admin"/>
<role rolename="manager-gui"/>
<role rolename="manager-script"/>
<user username="admin" password="admin" roles="admin,manager,manager-gui,manager-script"/>


8.  Add configuration to maven settings.xml 

a. open C:\Users\raylabs\Downloads\apache-maven-3.3.3-bin\apache-maven-3.3.3\conf\settings.xml
 <servers>
<server>
   <id>TomcatServer</id>
   <username>admin</username>
   <password>admin</password>
</server>
  </servers>

9. Import the project to eclipse 



10. Add configuration to pom.xml of the project

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.raylabs.maven</groupId>
  <artifactId>HelloMaven</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>HelloMaven Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
  </dependencies>
  <build>
    <finalName>HelloMaven</finalName>
    <plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>1.7</source>
<target>1.7</target>
</configuration>
</plugin>

<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>tomcat-maven-plugin</artifactId>
<version>1.1</version>
<configuration>
<url>http://localhost:8080/manager/text</url>
<server>TomcatServer</server>
<path>/HelloMaven</path>
</configuration>
</plugin>
</plugins>
  </build>
</project>

11. Start the tomcat server : startup.bat in bin folder

12. Run the command to deploy the application to tomcat
mvn clean package redeploy : Every time you make changes
mvn tomcat:deploy : First time
mvn tomcat:redeploy : After changes
mvn tomcat:undeploy : Remove the project.









Posted by at No comments:
Labels:

Friday, May 15, 2015

Oracle SOA BPM : Get jca reference properties using api

Some times we found out while using config plans to change schema for the package or procedure it was not reflecting.  In order to check in all the code, we had to manually go into EM console and check the JCA reference for schema name.

After some exploration, i found out that we can check schema names for all composites quickly  using soa facade api's ,below is the java code.












Posted by at No comments:
Labels: ,

Thursday, May 7, 2015

Work around when Audit trail is not visible in EM : Unable to display Flowtrace due to exception while initializing object, please review logs for detailed information

Some times when you open an instance in EM console, you might not be able to view the audit trail.

The error might be as below

Unable to display Flowtrace due to exception while initializing object, please review logs for detailed information.

>> Exception occured while retrieving the Flowtrace XML for the Composite Instance; ECID: 41343d22bdb09a74:-2967886c:144cf30c70b:-8000-000000000296dd8a
>> java.lang.NullPointerException
  at oracle.sysman.emai.model.sca.share.composite.FlowTraceModel.setFloatraceModelInfo(FlowTraceModel.java:185)
  at oracle.sysman.emai.model.sca.share.composite.FlowTraceModel.initialize(FlowTraceModel.java:90)
  at oracle.sysman.emai.model.sca.EMEntityModel._initializeEntityData(EMEntityModel.java:28)
  at oracle.sysman.emai.model.sca.common.SCAEntityObject.initializeSCAData(SCAEntityObject.java:124)
  at oracle.sysman.emai.model.sca.SCAObject.initializeData(SCAObject.java:74)
  at oracle.sysman.core.model.util.ModelUtil$EMObjectResetter.initOrResetEMObject(ModelUtil.java:319)
  at oracle.sysman.core.model.util.ModelUtil$EMObjectResetter.access$000(ModelUtil.java:280)
  at oracle.sysman.core.model.util.ModelUtil.initializeMBEMObjects(ModelUtil.java:231)
  at oracle.sysman.core.model.util.ModelUtil.initializeAllEMObjects(ModelUtil.java:128)
  at oracle.sysman.core.model.util.ModelUtil.initializeAllEMObjects(ModelUtil.java:101)
  at oracle.sysman.emSDK.adfext.ctlr.EMPagePhaseListener.doPrePrepareModel(EMPagePhaseListener.java:867)
  at oracle.sysman.emSDK.adfext.ctlr.EMPagePhaseListener.beforePhase(EMPagePhaseListener.java:795)
  at oracle.adfinternal.controller.lifecycle.ADFLifecycleImpl$PagePhaseListenerWrapper.beforePhase(ADFLifecycleImpl.java:558)
  at oracle.adfinternal.controller.lifecycle.LifecycleImpl.internalDispatchBeforeEvent(LifecycleImpl.java:100)
  at oracle.adfinternal.controller.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:191)
  at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener.startPageLifecycle(ADFPhaseListener.java:203)
  at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener$1.after(ADFPhaseListener.java:377)
  at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener.afterPhase(ADFPhaseListener.java:88)
  at oracle.adfinternal.controller.faces.lifecycle.ADFLifecyclePhaseListener.afterPhase(ADFLifecyclePhaseListener.java:53)
  at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:520)
  at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:207)
  at javax.faces.webapp.FacesServlet.service(FacesServlet.java:265)
  at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
  at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
  at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)
  at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
  at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
  at oracle.sysman.emSDK.license.LicenseFilter.doFilter(LicenseFilter.java:164)


I had to debug the issue, so i tried a new way.

1. Get all the components with id associated with the instance.
2. Now i had list of components with component instance id and i will get list of error messages





Now i know which component has error.

Whether its BPEL, BPMN, MEDIATOR, HUMAN TASK etc I will goto the respective service engines in EM console, put the component instance id and will be able to view the faults with audit trail of that particular component.













Posted by at No comments:
Labels: ,

Tuesday, April 28, 2015

Jenkins useful posts

1. Jenkins Setup and Hello World  : link
Posted by at No comments:
Labels:

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:

Thursday, April 2, 2015

Oracle SOA BPM : Task definition not found using namespace error


If you get  the below error

Task definition not found using namespace.[[
There is no active SCA composite with the referenced task definition. The provided task definition namespace is default/xyz!9.40/userTaskHT.
Verify that the composite containing the task definition is active.
 ORABPEL-30069

Task definition not found using namespace.
There is no active SCA composite with the referenced task definition. The provided task definition namespace is default/xyz!9.40/userTaskHT.
Verify that the composite containing the task definition is active.

at oracle.bpel.services.workflow.task.impl.WorkflowUtil.getTaskDefinitionId(WorkflowUtil.java:5038)
at oracle.bpel.services.workflow.task.impl.WorkflowUtil.setTaskDefinitionIdentifiers(WorkflowUtil.java:4943)
at oracle.bpel.services.workflow.task.impl.TaskService.initiateTask(TaskService.java:1286)
at oracle.bpel.services.workflow.task.impl.TaskService.initiateTask(TaskService.java:751)
at oracle.bpel.services.workflow.task.impl.TaskService.initiateTask(TaskService.java:679)
at sun.reflect.GeneratedMethodAccessor42858.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)



Solution : Redeployment with the same revision resolved the issue.


Also refer to one of the post i found recently on invalid task definition : here 
Posted by at No comments:
Labels: ,

Wednesday, March 11, 2015

Oracle SOA BPM Retrieve ldap user properties using expression.

If you want to retrieve ldap user properties like email, first name , last name etc, SOA suite provides Identity service functions to retrieve the same. The ldap is the security provider configured in weblogic server- Security realms.

You can retrieve some the properties below :

  1.    name
  2.    displayName
  3.    description
  4.    firstName
  5.    middleName
  6.    lastName
  7.    workNumber
  8.    homePhone
  9.    mobile
  10.    pager
  11.    fax
  12.    email
  13.    im
  14.    title



To use it in Jdeveloper use assign activity, select Expression Builder.






You can use the getUserproperty expression as below :


ids:getUserProperty($inputVariable.payload/ns3:userId,'lastName','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'name','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'title','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'email','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'firstName','jazn.com')








Posted by at No comments:
Labels: ,

Oracle SOA BPM : Queries for SOA INFRA


1. Query to find number of purgable instances and respective states according to dates
--version 1
select
to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
decode(state,
 1,'1 - Completed',
 2,'2 - Running with faults',
 3, '3 - Completed with faults',
 16, '16 - Running with terminated',
 17, '17 - Completed with terminated',
 19, '19 - Completed with faults and terminated',
 64, '64 - ?') "State",
count(distinct ecid)
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 to_char(trunc(partition_date,'mm'),'YYYY-mm'), state
ORDER BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE ASC;

-- version 2 group by date

select
to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
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(distinct ecid)
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 TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE
ORDER BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE ASC;

--version 3 group by count of 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;


2.  Query to find number of Running instances and respective states according to dates

--version 1
select to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
decode(state, 1,'1 - Completed',
 2,'2 - Running with faults',
 3, '3 - Completed with faults',
 16, '16 - Running with terminated',
 17, '17 - Completed with terminated',
 19, '19 - Completed with faults and terminated',
 64, '64 - ?', state) "State", count(distinct ecid)
FROM COMPOSITE_INSTANCE
group by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state
order by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state asc;


--version 2

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;




3.  Table queries

SELECT COUNT(1) FROM COMPOSITE_INSTANCE;--
SELECT COUNT(1) FROM CUBE_INSTANCE;--
SELECT COUNT(1) FROM WFTASK;--
select count(1) from DLV_MESSAGE;--

--Count from 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;

3. Calculate increase in size of database schema's

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'
GROUP BY C.TABLESPACE_NAME,C.SEGMENT_NAME,B.OBJECT_TYPE)
ORDER BY 3 DESC;



4. Current Size of each tables

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

5. Size allocated to soa infra and current size


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;

--All tables

select
                a.tablespace_name,
                round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
                round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
                b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
                (SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
                round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
                b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) -
                round(c.Free/1024/1024/1024))),2) FREE_GB,
                round(100*(SUM(a.bytes)/(1024*1024*1024) -
                round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
                b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
                dba_data_files a,
                sys.filext$ b,
                (SELECT
                               d.tablespace_name ,sum(nvl(c.bytes,0)) Free
                FROM
                               dba_tablespaces d,
                               DBA_FREE_SPACE c
                WHERE
                               d.tablespace_name = c.tablespace_name(+)
                               group by d.tablespace_name) c
WHERE
                a.file_id = b.file#(+)
                and a.tablespace_name = c.tablespace_name
GROUP BY A.TABLESPACE_NAME, C.FREE/1024
ORDER BY USED_PCT desc;


----------------------------------------------------------------------------------------------------
 Table space allocated to all schema's

select tablespace_name,used_percent, TABLESPACE_SIZE from dba_tablespace_usage_metrics order by USED_PERCENT desc;



----------------------------------------------------------------------------------------------------



6.  Performance of BPEL or BPM component in a day

SELECT DOMAIN_NAME,
COMPONENT_NAME,
DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') STATE,
TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),18,4))),'999990.000') AVERG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),18,4))),'999990.000') MINI,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),18,4))),'999990.000') MAXI,
COUNT(1) CNT
FROM CUBE_INSTANCE
WHERE CREATION_DATE >= SYSDATE-1
--AND COMPONENT_NAME LIKE '%%'
--AND COMPOSITE_NAME LIKE '%%'
GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE
ORDER BY COMPONENT_NAME, STATE;


7. Performance of Mediator component in a day.


SELECT SUBSTR(COMPONENT_NAME, 1, INSTR(COMPONENT_NAME,'/')-1)
PARTITON,
SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,
INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,
DECODE(COMPONENT_STATE, '0', 'COMPLETED', '16', 'STALE', '2',
'FAULTED', '4', 'RECOVERY NEEDED', '8', 'RUNNING') STATE,
TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') AVERG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MINI,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MAXI,
COUNT(1) CNT
FROM MEDIATOR_INSTANCE
WHERE CREATED_TIME >= SYSDATE-1
--AND COMPONENT_NAME LIKE '%%'
GROUP BY COMPONENT_NAME, COMPONENT_STATE
ORDER BY COMPONENT_NAME, COMPONENT_STATE;

8. Performance of each composite.

SELECT CMPST_ID,
TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') CREATION_DATE,
COMPONENT_NAME,
COMPONENTTYPE,
DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') STATE,
TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4)),'999990.000') DURTION
FROM CUBE_INSTANCE
WHERE TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI')
AND (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + 
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4)) > 10
--AND COMPONENT_NAME LIKE '%%'
--AND COMPOSITE_NAME LIKE '%%'
ORDER BY COMPONENT_NAME, CREATION_DATE;


SELECT COMPOSITE_INSTANCE_ID,
TO_CHAR(COMPOSITE_CREATION_DATE, 'YYYY-MM-DD HH24:MI') CREATION_DATE,
SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,
INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,
DECODE(COMPONENT_STATE, '0', 'COMPLETED', '16', 'STALE', '2','FAULTED', '4', 'RECOVERY NEEDED', '8', 'RUNNING') STATE,
TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4)),'999990.000') DURTION
FROM MEDIATOR_INSTANCE
WHERE TO_CHAR(COMPOSITE_CREATION_DATE, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI')
AND (TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) + 
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) + 
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4)) > 10
-- AND COMPONENT_NAME LIKE '%%'
ORDER BY COMPONENT_NAME, CREATION_DATE;


8. Slow performing queries in the database

SELECT SQL_FULLTEXT, SQL_ID, CHILD_NUMBER, DISK_READS, EXECUTIONS,
FIRST_LOAD_TIME, LAST_LOAD_TIME, ELAPSED_TIME FROM V$SQL ORDER BY
ELAPSED_TIME DESC;


9. select * from user_tab_modifications order by timestamp desc;


Other queries can be found here 












Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)