How to find out number of BPEL instances in SOA 11g dehydration store?

A common task for fusion admins would be to find out the number of BPEL instances in the SOA dehydration store,the SOA database. Basically this information is stored in a table called COMPOSITE_INSTANCE and the owner of the table id DEV_SOAINFRA schema.

There are two major ways of doing it:

1. From EM Console

For finding out the instance count from EM console:

a. Login to the SOA 11g EM console and expand the SOA folder on your left.This is shown below. Click on the soa-infra

b. On the right hand side of the screen click on Number of Instances as shown below:

c. The number of instances is displayed as shown below:

2. Select query on SOA dehydration table

 For finding out the number of BPEL instances in your SOA 11g Dehydration using SQL query follow  below steps:

a. Login to the SOA 11g Database using a SQL Client as DEV_SOAINFRA user.(Check out if your prefix ie DEV here is different)

b. Issue below command:

select count(*) from COMPOSITE_INSTANCE;

That would give you the number of instance present in the SOA dehydration store :)

Number of Instances for a specific Composite:


What if you wanted to find the number of instance for each composite,well use the below query

select count(*) from composite_instance where source_name='Composite Name'