Note - The queries below are for a given Composite name. Ensure you replace the 'COMPOSITE_NAME' within the WHERE clause with the composite you would like to target. You can also add further WHERE clauses to return results for multiple Composites.
-- QUERY 1 GET ALL INSTANCE STATES FROM CUBE_INSTANCE TABLE
SELECT (CASE
WHEN STATE=0 THEN 'State 0 - STATE INITIATED'
WHEN STATE=1 THEN 'State 1 - OPEN AND RUNNING'
WHEN STATE=2 THEN 'State 2 - OPEN AND SUSPENDED'
WHEN STATE=3 THEN 'State 3 - OPEN AND FAULTED'
WHEN STATE=4 THEN 'State 4 - CLOSED AND PENDING'
WHEN STATE=5 THEN 'State 5 - CLOSED AND COMPLETED'
WHEN STATE=6 THEN 'State 6 - CLOSED AND FAUTED'
WHEN STATE=7 THEN 'State 7 - CLOSED AND CANCELLED'
WHEN STATE=8 THEN 'State 8 - CLOSED AND ABORTED'
WHEN STATE=9 THEN 'State 9 - CLOSED AND STALE'
WHEN STATE=10 THEN 'State 10 - NON-RECOVERABLE'
ELSE STATE || ''
END) AS CUBE_INSTANCE_STATE, COUNT(*) AS NUM_OF_CUBE_INST
FROM CUBE_INSTANCE CI
WHERE CI.composite_name = 'COMPOSITE_NAME' --REPLACE WITH YOUR COMPOSITE NAME
GROUP BY STATE;
 |
| Query 1 sample result - Ran from Oracle SQL Developer |
-- QUERY 2 GET ALL INSTANCE STATES FROM COMPOSITE_INSTANCE TABLE
SELECT (CASE
WHEN STATE=0 THEN 'State 0 - RUNNING'
WHEN STATE=1 THEN 'State 1 - COMPLETED'
WHEN STATE=2 THEN 'State 2 - RUNNING WITH FAULTS'
WHEN STATE=3 THEN 'State 3 - COMPLETED WITH FAULTS'
WHEN STATE=4 THEN 'State 4 - RUNNING WITH RECOVERY REQUIRED'
WHEN STATE=5 THEN 'State 5 - COMPLETED WITH RECOVERY REQUIRED'
WHEN STATE=6 THEN 'State 6 - RUNNING WITH FAULTS AND RECOVERY
REQUIRED'
WHEN STATE=7 THEN 'State 7 - COMPLETED WITH FAULTS AND RECOVERY
REQUIRED'
WHEN STATE=8 THEN 'State 8 - RUNNING WITH SUSPENDED'
WHEN STATE=9 THEN 'State 9 - COMPLETED AND SUSPENDED'
WHEN STATE=10 THEN 'State 10 - RUNNING WITH FAULTS AND SUSPENDED'
WHEN STATE=11 THEN 'State 11 - COMPLETED WITH FAULTS AND SUSPENDED'
WHEN STATE=12 THEN 'State 12 - RUNNING WITH RECOVERY REQUIRED AND
SUSPENDED'
WHEN STATE=13 THEN 'State 13 - COMPLETED WITH RECOVERY REQUIRED AND
SUSPENDED'
WHEN STATE=14 THEN 'State 14 - RUNNING WITH FAULTS, RECOVERY
REQUIRED, AND SUSPENDED'
WHEN STATE=15 THEN 'State 15 - COMPLETED WITH FAULTS, RECOVERY
REQUIRED, AND SUSPENDED'
WHEN STATE=16 THEN 'State 16 - RUNNING WITH TERMINATED'
WHEN STATE=17 THEN 'State 17 - COMPLETED WITH TERMINATED'
WHEN STATE=18 THEN 'State 18 - RUNNING WITH FAULTS AND TERMINATED'
WHEN STATE=19 THEN 'State 19 - COMPLETED WITH FAULTS AND TERMINATED'
WHEN STATE=20 THEN 'State 20 - RUNNING WITH RECOVERY AND TERMINATED'
WHEN STATE=21 THEN 'State 21 - COMPLETED WITH RECOVERY REQUIRED AND
TERMINATED'
WHEN STATE=22 THEN 'State 22 - RUNNING WITH FAULTS, RECOVERY
REQUIRED, AND TERMINATED'
WHEN STATE=23 THEN 'State 23 - COMPLETED WITH FAULTS, RECOVERY
REQUIRED AND TERMINATED'
WHEN STATE=24 THEN 'State 24 - RUNNING WITH SUSPENDED AND
TERMINATED'
WHEN STATE=25 THEN 'State 25 - COMPLETED WITH SUSPENDED AND
TERMINATED'
WHEN STATE=26 THEN 'State 26 - RUNNING WITH FAULTED, SUSPENDED, AND
TERMINATED'
WHEN STATE=27 THEN 'State 27 - COMPLETED WITH FAULTS, SUSPENDED, AND
TERMINATED'
WHEN STATE=28 THEN 'State 28 - RUNNING WITH RECOVERY REQUIRED,
SUSPENDED, AND TERMINATED'
WHEN STATE=29 THEN 'State 29 - COMPLETED WITH RECOVERY REQUIRED,
SUSPENDED, AND TERMINATED'
WHEN STATE=30 THEN 'State 30 - RUNNING WITH FAULTED, RECOVERY
REQUIRED, SUSPENDED, AND TERMINATED'
WHEN STATE=31 THEN 'State 31 - COMPLETED WITH FAULTED, RECOVERY
REQUIRED, SUSPENDED, AND TERMINATED'
WHEN STATE=32 THEN 'State 32 - UNKNOWN'
ELSE STATE || ''
END) AS COMPOSITE_INSTANCE_STATE, COUNT(*) AS NUM_OF_COMP_INST
FROM COMPOSITE_INSTANCE CI
WHERE CI.SOURCE_NAME = 'COMPOSITE_NAME' --REPLACE WITH YOUR COMPOSITE NAME
GROUP BY STATE;
Here is a useful link that re-iterates the State values and their descriptions for some of the tables within SOA-INFRA: Click here
 |
| Query 2 sample result - Ran from Oracle SQL Developer |
Here is a useful link that re-iterates the State values and their descriptions for some of the tables within SOA-INFRA: Click here
