Database tables used to store the instance details

I was working on a task this week, where I had to extract the RAW xml in the audit tab of an instance. Since all the details that are rendered on the BPEL console are from the database, I started to explore some details on the tables used in SOA. The tables are more or less common to 10g as well as 11g, which very little variations. I was going though some of the tables present in the database and below is the information I gathered.  I have shown below some non empty and key fields from the table. There are more fields available in the table and can be viewed from the database:

CUBE_INSTANCE
This table stores the basic information of an instance such as State, Domain name, Composite name, composite revision, Component name etc. A CI Key is created for every Composite Id and the CI key is used in other tables as the reference

CUBE_SCOPE
It stores the data available in the scope of an instance like the bpel variables that have been created, values of those variables etc.

AUDIT_TRAIL
This stores the complete the audit trail of an instance, provided the auditDetailThreshold is not reached. If the auditDetailThreshold is reached, the details are spilled to audit_details table. The instance details are stored in the RAW format and you would have to join the columns together and uncompress the RAW format to view in the readable xml format. The column LOG contains the RAW message

AUDIT_DETAILS
The details like the assign activities are stored here. The information that goes into the audit tables are determined from the auditLevel property set in the domain or for a composite


The difference between 10g and 11g is that, as far 11g is concerned, the EM is one stop for viewing all instances. So instances are stored in the tables mentioned above in the schema SOAINFRA. The Mediator instances can be viewed in the MEDIATOR_INSTANCE, MEDIATOR_* in the same schema. On the other hand, 10g had separate tables for tracking BPEL and ESB instances. The tables mentioned above are used for storing bpel instance details and can be found under the schema ORABPEL. The ESB instance deatils are stored in the tables mentioned below and can be found under ORAESB schema:

ESB_ACTIVITY, ESB_TRACKING_FIELD_VALUE, ESB_FAULTED_INSTANCE, ESB_TRANSACTION_STATUS

There are some interesting links that provide details on these tables. Some of them are below:

Tables used in BPEL instance tracking
Oracle Cookbook for SOA 10g
Table used in SOA 10g ESB instance tracking

Peace!

Cheers,
-AR