Querying Composite Instance States in SOA-INFRA Dehydration Store forOracle SOA Suite 11g Processes

If you have a large number of composite instances present for a single or multiple composites the Enterprise Manager will not be a very ideal place to look at if we want to know the states of these instances.

We can however connect directly to the SOA_INFRA schema for our Oracle SOA Suite installation to execute a few scripts to view instance states of various composites.

Here are the database scripts available to find composite instances in various states in the CUBE_INSTANCE table.

The table below shows the various State Value for composite instances in the CUBE_INSTANCE table of the SOA_INFRA schema in the dehydration store.

STATE VALUE	STATE	STATE MEANING
0	INITIATED	State value for an instance that has just been created. The instance will only have this value as its state after it has been created by the process domain.
1	OPEN AND RUNNING	State value for an instance that has been created and has active activities executing. The instance is not in an exception or error condition.
2	OPEN AND SUSPENDED	State value for an instance that is unavailable. Performers of any of the activities that belong to this instance cannot take any action until the instance has returned to the running state.
3	OPEN AND FAULTED	State value for an instance that has an activity that has thrown an exception. When an activity throws an exception, the instance is flagged as being in an exception state until the exception is bubbled up, caught and handled.
4	CLOSED AND PENDING	State value for an instance that has started its cancellation procedure. Since cancelling an instance may involve a great deal of business logic, the amount of time the entire cancellation process may take may be anywhere from seconds to days. During this time, the instance is said to be pending cancellation; an instance may not be acted upon during this time.
5	CLOSED AND COMPLETED	State value for an instance that has been completed. All activities belonging to this instance have also been completed.
6	CLOSED AND FAULTED	State value for an instance that has an activity that has thrown an exception while the instance is being cancelled. This state is equivalent to <code>STATE_OPEN_FAULTED</code> except that when the exception is resolved, the state transitions back to <code>CLOSED_PENDING_CANCEL</code> rather than <code>STATE_OPEN_RUNNING</code>
7	CLOSED AND CANCELED	State value for an instance that has been cancelled. All activities belonging to this instance have also been cancelled.
8	CLOSED AND ABORTED	State value for an instance that has been aborted due to administrative control. All activities belonging to this instance are also moved to the aborted state.
9	CLOSED AND STALE	State value for an instance who's process has been changed since the process was last accessed. No actions may be performed on the instance. All activities that belong to this instance are also moved to the stale state.
10	NON RECOVERABLE	State value of instance that has failed and is marked as non recoverable.

The database query that can fetch and tabulate the number of instances in various states in the CUBE_INSTANCE table

SELECT (CASE WHEN STATE=1 THEN 'OPEN AND RUNNING'
WHEN STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN STATE=3 THEN 'OPEN AND FAULTED'
WHEN STATE=4 THEN 'CLOSED AND PENDING'
WHEN STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN STATE=6 THEN 'CLOSED AND FAUTED'
WHEN STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN STATE=8 THEN 'CLOSED AND ABORTED'
WHEN STATE=9 THEN 'CLOSED AND STALE'
WHEN STATE=10 THEN 'NON-RECOVERABLE'
ELSE STATE || ''
END) AS STATE, COUNT(*) AS NUM_OF_CUBE_INST FROM CUBE_INSTANCE GROUP BY STATE;

In case you would like the same count of instances for a particular COMPOSITE application then extend the above sql script by adding additional filters to it.

You can add filters on CUBE_INSTANCE table based on COMPOSITE_NAME, COMPOSITE_REVISION, COMPONENTTYPE (BPEL/BPM/MEDIATOR) etc.