Oracle SOA Suite Dehydration Store Tables

These are the important table of Oracle SOA Suite dehydration store that are used to capture the Meta Data. A brief description of the table is also given.

WFTask	This table is Metadata table for workflow tasks
Process	This is the meta data table for information about the processes that are deployed in oracle application server.
WFTaskHistory	This table stores the history of the workflow tasks. The OUTCOME table will give you the details of the outcome the ASSIGNEE has chosen for each revision of the task.
Domain	This table contains the domain reference number that is created for every domain.
domain_properties	All the domain level properties that are modified in the BPEL CONSOLE are actually stored as values here.
suitcase_bin	Every suitcase that is deployed in the SOA server is stored in the binary format in the Bin column of this table.
process_log	Any process maintenance work (marking as default, Compiling etc) are logged in this table. This table is used for scouting for any issues with processes.
test_details	The test suite details are stored in this table.

These are the important table of Oracle SOA Suite dehydration store that are used to capture the Transactional Data. A brief description of the table is also given.

Table	Description
CUBE_INSTANCE	This table contains the instance wise information for every instance that is being initiated. The CIKEY is the primary key of the table and this is the unique reference for a initiated instance. The DOMAIN_REF is used to refer the domain in which the initiated process belongs to. The PROCESS_ID gives the name of the process. The revision tag denotes the version of the process. The CREATION_DATE denotes the date in which the instance is created. The STATE denotes whether the instance is stale/completed successfully/error. The CONVERSATION_ID is an identifier that is used in correlation of instances in WS-Addressing. This can also be used in identifying an instance. The PARENT_ID is used to denote the CIKEY of the parent instance that initiated this process(if any).
CUBE_SCOPE	The SCOPE_BIN column of this table is used to store the values of all variables declared in BPEL process and it values currently in runtime.
DLV_MESSAGE	This table is an important part of correlation that occurs in BPEL. All incoming message’s metadata is stored in this table.
DLV_MESSAGE_BIN	This table is an important part of correlation that occurs in BPEL. All incoming message’s payload is stored in this table.
INVOKE_MESSAGE	This table is an important part of correlation that occurs in BPEL. All outgoing message’s metadata is stored in this table.
INVOKE_MESSAGE_BIN	This table is an important part of correlation that occurs in BPEL. All outgoing message’s payload is stored in this table.
AUDIT_TRAIL	This table is used to store information of the xml that is rendered in the BPEL Console. The LOG column has this xml.
TASK	This table concerns with the human task that are created. Every task that is created is logged in this table. The ASSIGNEE denotes the user to whom the task is assigned.

The below are the values of the STATE column of the CUBE_INSTANCE table.

I use this extensively to monitor current status of the queue.

0 --> initiated

1 --> open and running

2 --> open and suspended

3 --> open and faulted

4 --> closed and pending

5 --> closed and completed

6 --> closed and faulted

7 --> closed and canceled

8 --> closed and aborted

9 --> closed and stale

Hope I don’t need to write SQL queries. You can frame with the all the data provided to monitor. These tables can also be used through BPEL API’s to design custom accesses.