Thursday, February 2, 2012

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'

Now you can folow us on facebook and post your comments/views and questions for expert advise. Check this out facebook

Find us on facebook here


  1. yes very useful hints to find out the instances quickly

  2. Are you looking for free Google+ Circles?
    Did you know that you can get them ON AUTOPILOT & TOTALLY FREE by using Like 4 Like?

  3. how to check with using if , i mean i want to apply condition check on the basis of count

  4. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check