Tuesday 9 October 2012

BPEL 2.0 Close Look


The BPEL4WS 1.1 specification was submitted to OASIS back in 2004 and after three years of work by one of the largest technical committees at OASIS, WS-BPEL 2.0 finally became an OASIS standard on April 12, 2007. While adoption of the BPEL language has not been gated by the 2.0 standard or the OASIS stamp of approval - there are thousands of successful BPEL projects and deployments today - the formal publication of the standard is an important milestone and will further accelerate BPEL's adoption and vendor support.
A lot has been already written about the new features in WS-BPEL 2.0 on various blogs, Web sites, and magazine articles In this post, I'll drill down into the next level of detail regarding the new features in WS-BPEL 2.0 using concrete examples wherever possible. Throughout this article, we abbreviate BPEL4WS 1.1 as BPEL 1.1 and WS-BPEL 2.0 as BPEL 2.0.
BPEL 2.0 Overview
At a high level BPEL is an XML language that provides a rich set of activities to describe an executable business process. The processes and activities can be synchronous or asynchronous, short-lived or long-running; BPEL provides a sophisticated language for defining the process flow, system interactions, data manipulation, exception handling, compensation rules, etc. First, we will briefly summarize the important features of the BPEL standard, explicitly calling out what is new or changed in BPEL 2.0: (Figure 1)
•  Service interaction activities: A BPEL process is automatically a Web Service and receives inputs via <receive> or <pick> activities. A process can send back a synchronous Web Service response using <reply>. The <invoke> activity is available to invoke an external service, as described below, but also to respond asynchronously to a client.
•  Event handling constructs: A process can get input requests at non-deterministic points during process execution with <eventHandlers> using <onEvent> for messages (new in BPEL - replaces <onMessage> from BPEL 1.1) or <onAlarm> for time-triggered events. <wait> can wait for a specified time or until a deadline is reached and <receive> can wait for events at pre-determined points in the process.
•  Back-end system interactions: Interactions with external services are represented as <partnerLinks>. Asynchronous conversational interactions can be correlated using <correlationSet> or the WS-Addressing standard. A process maintains its state using <variables> that can be defined at global or local scope. BPEL 2.0 makes it easier to map process variables to WSDL message variables. It also provides the new <messageExchange> activity to distinguish instances of similar conversations (request/response pairs).
•  Data manipulation activities: BPEL 2.0 adds a new simplified XPath notation ($variableName) replacing the getVariableData() function. Besides the existing <assign> activity to map data between variables, BPEL 2.0 provides a doXSLTransform() function to natively support XSL Transformations. A <validate> activity has been added for schema validations. These additions have already been time tested, having been implemented as extensions in vendor implementations of BPEL for quite some time now.
•  Process structural flow related activities: BPEL includes basic structural activities similar to other workflow or programming languages for sequencing, iteration, and branching. BPEL 1.1 supported <sequence> for sequential execution, <flow> for parallel branches, and <while> for looping. BPEL 2.0 adds <if> / <else>, <repeatUntil> and <forEach> for richer flow control syntax. In particular, the new <forEach> construct now supports dynamic parallelism (executing N activities in parallel, when the value N is not known until execution time). This was not supported in BPEL 1.1 except through vendor extensions.
•  Exception handling and recovery constructs: Exceptions, represented as faults, are propagated using <throw>, and BPEL 2.0 adds <rethrow> to provide more explicit control over exception management patterns. In <faultHandlers>, faults can be detected using <catch> and <catchAll>. A process can undo completed work through <compensationHandlers> and the <compensate> activity; BPEL 2.0 adds <compensateScope> to clarify the syntax of BPEL 1.1's overloading of the <compensate> activity. BPEL 2.0 also adds <terminationHandlers> to enable processes to specify the logic to be invoked just prior to the termination of a process.
•  Extensibility: BPEL 2.0 adds <extensionAssignOperation> to extend the standard <assign> activity; it also provides <extensionActivity> to add new activity types. This is another area where the 2.0 standard now explicitly covers things that vendor implementations were already doing. BPEL 2.0 also now supports <import> and <documentation>.
Kitchens Online Use Case
To illustrate some of the BPEL features, we will use the example of Kitchens Online, a fictitious Internet-based kitchen-remodeling solution. Kitchens Online provides a Web site where customers can select appliances and cabinets and schedule delivery and installation. Kitchens Online doesn't carry any stock and sources the different components from various vendors; however it still wants to keep inventory information as up-to-date as possible. Complicating matters, the vendors Kitchens Online's suppliers don't always have automated inventory systems - in many instances, a person needs to check manually whether a given item is available. When a customer places an order, Kitchens Online tries to reserve all the needed components from its vendors. If it can't reserve a piece, the customer is notified and can then change or cancel the order.
This example highlights the following advanced requirements:
  • Dynamic Parallelism - handling of different components (line items) of an Order in parallel
  • Change handling (out-of-band events) - a delivery schedule may change while an Order is in process
  • Compensation - reversing credit card charges and releasing reserved inventory when a component is out-of-stock
Since this article is focused on the BPEL 2.0 standard, and BPEL is an XML language, we'll examine the BPEL "source code" in XML format. Of course most developers won't hand-code BPEL in the XML format - they'll use visual tools built on top of the standard. Several vendors and open source implementations of such tools exist, including some that enable developers to switch back and forth between a visual model of a BPEL process and the underlying BPEL source. Here we'll examine the underlying XML source, so get ready!
Dynamic Parallelism
The Kitchens Online requirement to process the multiple items of an Order in parallel is a very common pattern. The <flow> activity introduced in BPEL 1.1 supported parallelism, but only when the number of parallel branches was known at design time. Here, an Order may have a variable number of items and therefore the number of parallel branches won't be known until runtime. What's needed is the equivalent of a typical programming language "for" loop where all the iterations of the loop are executed in parallel.
To address this pattern, BPEL 2.0 introduces the <forEach> activity. As the name indicates, this activity causes the enclosed scope to iterate between the <startCounterValue> and <finalCounterValue> inclusive (i.e., N+1 times, where N is the difference between the two). The <forEach> activity has an attribute parallel that when set to yes causes all the branches to be executed in parallel and a counterName attribute that specifies an integer variable that will hold the counter value for each particular iteration (1 for the first iteration in the example below, 2 for the second, etc.).

Tuesday 27 March 2012

Description of Dehydration Store Tables used in Oracle BPEL PM


cube_instance - stores instance metadata, eg. instance creation date, current
state, title, process identifier

cube_scope - stores the scope data for an instance ... all the variables
declared in the bpel flow are stored here, as well as some internal objects to
help route logic throughout the flow.

work_item - stores activities created by an instance ... all BPEL activities in
a flow will have a work_item created for it. This work item row contains meta
data for the activity ... current state, label, expiration date (used by wait
activities) ... when the engine needs to be restarted and instances recovered,
pending flows are resumed by inspecting their unfinished work items.

document - stores large XML variables. If a variable gets to be larger than a
specific size (configurable via the largeDocumentThreshold property via the
domain configuration page) then the variable is stored in this table to
alleviate loading/saving time from the cube_scope table.


audit_trail - stores the audit trail for instances. The audit trail viewed
from the console is modelled from an XML document. As the instance is worked
on, each activity writes out events to the audit trail as XML which is compress
ed and stored in a raw column. Querying the audit trail via the API/console wil
l join the raw columns together and uncompress the contents into a single XML do
cument.

audit_details - audit details can be logged via the api ... by default
activities such as assign log the variables as audit details (this behavior can
be set via the auditLevel property on the domain configuration page). Details a
re separated from the audit trail because they tend to be very large in size ...
if the user wishes to view a detail they click a link from the audit trail page
and load the detail separately. There is a threshold value for details too ...
if the size of a detail is larger than a specific value (see auditDetailThreshol
d) then it is place in this table, otherwise it is merged into the audit trail r
ow.

dlv_message - callback messages are stored here. All non-invocation messages
are saved here upon receipt. The delivery layer will then attempt to correlate
the message with the receiving instance. This table only stores the metadata
for a message. (eg. current state, process identifier, receive date).

dlv_message_bin - stores the payload of a callback message. The metadata of a
callback message is kept in the dlv_message table, this table only stores the
payload as a blob. This separation allows the metadata to change frequently
without being impacted by the size of the payload (which is stored here and
never modified).

dlv_subscription - stores delivery subscriptions for an instance. Whenever an i
nstance expects a message from a partner (eg. receive, onMessage) a subscription
is written out for that specific receive activity. Once a delivery message is r
eceived the delivery layer attempts to correlate the message with the intended s
ubscription.

invoke_message - stores invocation messages, messages which will result in the
creation of a instance. This table only stores the metadata for an invocation
message (eg. current state, process identifier, receive date).

invoke_message_bin - stores the payload of an invocation message. Serves the
same purpose the dlv_message_bin table does for dlv_message.

task - stores tasks created for an instance. The TaskManager process keeps its
current state in this table. Upon calling invoking the TaskManager process, a
task object is created, with a title, assignee, status, expiration date, etc...
When updates are made to the TaskManager instance via the console the
underlying task object in the db is changed.

schema_md - (just added via patch delivered to Veerle) contains metadata about
columns defined in the orabpel schema. Use case driving this feature was how
to change the size of a custom_key column for a cube_instance row? Changing
the db schema was simple but the engine code assumed a certain length and
truncated values to match that length to avoid a db error being thrown. Now, co
lumn lengths are defined in this table instead of being specified in the code.
To change a column length, change the column definition in the table, then chang
e the value specified in this table, then restart the server.


Column-by-column description:

table ci_id_range

- next_range (integer) - instance ids in the system are allocated on a block
basis ... once all the ids from a block have been allocated, another block is
fetched, next_range specifies the start of the next block.


table cube_instance

- cikey (integer) - primary key ... foreign key for other tables
- domain_ref (smallint) - domain identifier is encoded as a integer to save
space, can be resolved by joining with domain.domain_ref.
- process_id (varchar) - process id
- revision_tag (varchar) - revision tag
- creation_date (date)
- creator (varchar) - user who created instance ... currently not used
- modify_date (date) - date instance was last modified
- modifier (varchar) - user who last modified instance ... currently not used
- state (integer) - current state of instance, see com.oracle.bpel.client.
IInstanceConstants for values
- priority (integer) - current instance priority (user specified, has no impact
on engine)
- title (varchar) - current instance title (user specified, no engine impact)
- status (varchar) - current status (user specified)
- stage (varchar) - current stage (user specified)
- conversation_id (varchar) - extra identifier associated with instance, eg. if
passed in via WS-Addressing or user specified custom key.
- root_id (varchar) - the conversation id of the instance at the top of the
invocation tree. Suppose A -> B -> C, root( B ) = A, root( C ) = A, parent( B )
= A, parent( C ) = B. This instance, instance at the top of the tree will not
have this set.
- parent_id (varchar) - the conversation id of the parent instance that created
this instance, instance at the top of the tree will not have this set.
- scope_revision (integer) - internal checksum of scope bytes ... used to keep
caches in sync
- scope_csize (integer) - compressed size of instance scope in bytes
- scope_usize (integer) - uncompressed size of instance scope in bytes
- process_guid (varchar) - unique identifier for the process this instance be
longs to ... if changes need to be made for all instances of a process, this col
umn is used to query (eg. stale process).
- process_type (integer) - internal
- metadata (varchar) - user specified


table cube_scope

- cikey (integer) - foreign key
- domain_ref (integer) - domain identifier
- modify_date (date) - date scope last modified
- scope_bin (blob) - scope bytes


table work_item

- cikey (integer) - foreign key
- node_id (varchar) - part of work item composite key, identifier for bpel
activity that this work item created for
- scope_id (varchar) - part of work item composite key, identifier for internal
scope that this work item created for (note this is not the scope declared in
bpel, the engine has an internal scope tree that it creates for each instance,
bpel scopes will map to an internal scope but there will be other internal
scopes that have no mapping to the bpel definition).
- count_id (integer) - part of work item composite key, used to distinguish
between work items created from same activity in the same scope.
- domain_ref (integer) - domain identifier
- creation_date (date)
- creator (varchar) - user who created work item ... currently not used
- modify_date (date) - date work item was last modified
- modifier (varchar) - user who last modified work item ... currently not used
- state (integer) - current state of work item, see com.oracle.bpel.client.
IActivityConstants for values
- transition (integer) - internal use, used by engine for routing logic
- exception (integer) - no longer used
- exp_date (date) - expiration date for this work item; wait, onAlarm
activities are implemented as expiration timers.
- exp_flag (integer) - set if a work item has been called back by the
expiration agent (ie. expired).
- priority (integer) - priority of work item, user specified, no engine impact
- label (varchar) - current label (user specified, no engine impact)
- custom_id (varchar) - custom identifier (user specified, no engine impact)
- comments (varchar) - comment field (user specified, no engine impact)
- reference_id (varchar) -
- idempotent_flag (integer) - internal use
- process_guid (varchar) - unique identifier for the process this work item
belongs to ... if changes need to be made for all instances of a process, this
column is used to query (eg. stale process).


table document

- dockey (varchar) - primary key for document
- cikey (integer) - foreign key
- domain_ref (integer) - domain identifier
- classname (varchar) - no longer used
- bin_csize (integer) - compressed size of document in bytes
- bin_usize (integer) - uncompressed size of document in bytes
- bin (blob) - document bytes
- modify_date (date) - date document was last modified


table audit_trail

- cikey (integer) - foreign key
- domain_ref - domain identifier
- count_id (integer) - many audit trail entries may be made for each instance,
this column is incremented for each entry per instance.
- block (integer) - when the instance is dehydrated, the batched audit trail
entries up to that point are written out ... this block ties together all rows
written out at one time.
- block_csize (integer) - compressed size of block in bytes
- block_usize (integer) - uncompressed size of block in bytes
- log (raw) - block bytes


table audit_details

- cikey (integer) - foreign key
- domain_ref (integer) - domain identifier
- detail_id (integer) - part of composite key, means of identifying particular
detail from the audit trail
- bin_csize (integer) - compressed size of detail in bytes
- bin_usize (integer) - uncompressed size of detail in bytes
- bin (blob) - detail bytes


table dlv_message

- conv_id (varchar) - conversation id (correlation id) for the message...this
value is used to correlate the message to the subscription.
- conv_type (integer) - internal use
- message_guid (varchar) - unique identifier for the message...each message
received by the engine is tagged with a message guid.
- domain_ref (integer) - domain identifier
- process_id (varchar) - identifier for process to deliver the message to
- revision_tag (varchar) - identifier for process revision
- operation_name (varchar) - operation name for callback port.
- receive_date (date) - date message was received by engine
- state (integer) - current state of message ... see com.oracle.bpel.client.
IDeliveryConstants for values
- res_process_guid (varchar) - after the matching subscription is found, the
process guid for the subscription is written out here. - res_subscriber
(varchar) - identifier for matching subscription once found.


table dlv_message_bin

- message_guid (varchar) - unique identifier for message
- domain_ref (integer) - domain identifier
- bin_csize (integer) - compressed size of delivery message payload in bytes
- bin_usize (integer) - uncompressed size of delivery message payload in bytes
- bin (blob) - delivery message payload


table dlv_subscription

- conv_id (varchar) - conversation id for subscription, used to help correlate
received delivery messages.
- conv_type (integer) - internal use
- cikey (integer) - foreign key
- domain_ref (integer) - domain identifier
- process_id (varchar) - process identifier for instance
- revision_tag (varchar) - revision tag for process
- process_guid (varchar) - guid for process this subscription belongs to
- operation_name (varchar) - operation name for subscription (receive,
onMessage operation name).
- subscriber_id (varchar) - the work item composite key that this subscription
is positioned at (ie. the key for the receive, onMessage work item).
- service_name (varchar) - internal use
- subscription_date (date) - date subscription was created
- state (integer) - current state of subscription ... see com.oracle.bpel.
client.IDeliveryConstants for values
- properties (varchar) - additional property settings for subscription


table invoke_message

- conv_id (varchar) - conversation id for message, passed into system so
callbacks can correlate properly.
- message_guid (varchar) - unique identifier for message, generated when
invocation message is received by engine.
- domain_ref (integer) - domain identifier
- process_id (varchar) - identifier for process to deliver the message to
- revision_tag (varchar) - revision tag for process
- operation_name (varchar) - operation name for receive activity
- receive_date (date) - date invocation message was received by engine
- state - current state of invocation message, see com.oracle.bpel.client.
IDeliveryConstants for values
- priority (integer) - priority for invocation message, this value will be used
by the engine dispatching layer to rank messages according to importance ...
lower values mean higher priority ... messages with higher priority are dispatch
ed to threads faster than messages with lower values.
- properties (varchar) - additional property settings for message


table invoke_message_bin

- message_guid (varchar) - unique identifier for message
- domain_ref (integer) - domain identifier
- bin_csize (integer) - compressed size of invocation message payload in bytes
- bin_usize (integer) - uncompressed size of invocation message payload in bytes
- bin (blob) - invocation message bytes


table task

- domain_ref (integer) - domain identifier
- conversation_id (varchar) - conversation id for task instance ... allows task
instance to callback to client
- title (varchar) - current title for task, user specified
- creation_date (date) - date task was created
- creator (varchar) - user who created task
- modify_date (date) - date task was last modified
- modifier (varchar) - user who last modified task
- assignee (varchar) - current assignee of task, user specified, no engine
impact
- status (varchar) - current status, user specified, no engine impact
- expired (integer) - flag is set if task has expired
- exp_date (date) - expiration date for task, expiration actually takes place
on work item in TaskManaged instance, upon expiration task row is updated
- priority (integer) - current task priority, user specified, no engine impact
- template (varchar) - not used
- custom_key (varchar) - user specified custom key
- conclusion (varchar) - user specified conclusion, no engine impact


List of Dehydration tables in Oracle SOA 11G .


TNAME TABTYPE
XREF_DATA TABLE
XREF_DELETED_DATA TABLE
WLI_QS_REPORT_ATTRIBUTE TABLE
WLI_QS_REPORT_DATA TABLE
BPM_AUDIT_SEQUENCE TABLE
BPM_AUDIT_QUERY_METADATA TABLE
BPM_AUDIT_QUERY_VAR_MD TABLE
BPM_AUDIT_QUERY TABLE
BPM_AUDIT_QUERY_VARMD_EXT1 TABLE
BPM_AUDIT_QUERY_VARVALS_EXT1 TABLE
BPM_AUDIT_QUERY_VAR_RANGES TABLE
BPM_CUBE_SEQUENCE TABLE
BPM_CUBE_ORGUNIT TABLE
BPM_CUBE_ORGUNIT_ASSOC TABLE
BPM_CUBE_PROCESS TABLE
BPM_CUBE_PROCESS_PARTNER TABLE
BPM_CUBE_PARTNER_SERVICE TABLE
BPM_CUBE_GLOBALCREATETASK TABLE
BPM_CUBE_ACTIVITY TABLE
BPM_CUBE_ACTIVITY_PROPERTY TABLE
BPM_CUBE_ACTIVITY_SEQFLOW TABLE
BPM_CUBE_LASTSNAPSHOT TABLE
BPM_CUBE_ROLE TABLE
IP_QTAB TABLE
AQ$_IP_QTAB_S TABLE
AQ$_IP_QTAB_T TABLE
AQ$IP_QTAB_S VIEW
AQ$_IP_QTAB_H TABLE
SYS_IOT_OVER_89366 TABLE
AQ$_IP_QTAB_G TABLE
AQ$_IP_QTAB_I TABLE
AQ$_IP_QTAB_F VIEW
AQ$IP_QTAB VIEW
AQ$IP_QTAB_R VIEW
EDN_LOG_MESSAGES TABLE
EDN_LOG_ENABLED TABLE
EDN_EVENT_AGENTS TABLE
B2B_DATA_STORAGE TABLE
B2B_WIRE_MESSAGE TABLE
B2B_BUSINESS_MESSAGE TABLE
B2B_EXT_BUSINESS_MESSAGE TABLE
B2B_APP_MESSAGE TABLE
B2B_CONTROL_NUMBER TABLE
B2B_PENDING_MESSAGE TABLE
B2B_BATCH_EVENT TABLE
B2B_TRANSPORT_MANAGER TABLE
B2B_HA_REGISTRY TABLE
B2B_LIFECYCLE TABLE
B2B_SEQUENCE_MANAGER TABLE
B2B_CONTROL_NUMBER_SET TABLE
B2B_CTL_NUMBER TABLE
B2B_BUSINESS_MESSAGE_RETRY TABLE
B2B_PARAMETER TABLE
B2B_PURGE_TEMP_BM TABLE
B2B_PURGE_TEMP_WM TABLE
B2B_PURGE_TEMP_AM TABLE
B2B_AUDIT_TEMP TABLE
B2B_LOCK_REGISTRY TABLE
B2B_INSTANCEMESSAGE VIEW
WFAPPROVALGROUPS TABLE
WFAPPROVALGROUPMEMBERS TABLE
WFPRODUCTIVITY_VIEW VIEW
WFUNATTENDEDTASKS_VIEW VIEW
WFTASKCYCLETIME_VIEW VIEW
WFTASKPRIORITY_VIEW VIEW
WFTASKASSIGNMENTHISTORY_VIEW VIEW
BPM_HOLIDAY_RULE TABLE
BPM_HOLIDAYS TABLE
BPM_CALENDAR_RULE TABLE
BPM_CALENDAR_WORKDAY TABLE
BPM_EXT_USER_PROPERTY_GLOBAL TABLE
BPM_EXT_USER_PROP_AVLBL_COL TABLE
BPM_EXT_USER_PROPERTY_VALUE TABLE
BPM_ORGANIZATIONAL_UNIT TABLE
BPM_OU_USER TABLE
BPM_OU_MANAGER TABLE
BPM_APP_ROLE_OU_CAL_ASSOC TABLE
BPM_PP_ENTITY_GS_MAP TABLE
BPM_LOGICAL_PEOPLE_GROUP TABLE
BPM_LPG_MEMBER TABLE
DOCUMENTPACKAGEINSTANCE TABLE
DOCUMENTPACKAGEPROPERTIES TABLE
DOCUMENTPACKAGEPROPERTIESMAP TABLE
DOCUMENTPACKAGEDEFINITION TABLE
DOCUMENTDEFINITION TABLE
DOCUMENTINSTANCE TABLE
DOCUMENTPACKAGEPROCESS TABLE
BPM_PRESENTATION_GRANT TABLE
WFUSERTASKVIEW TABLE
WFUSERTASKVIEWGRANT TABLE
WFUSERPREFERENCE TABLE
BPM_USERAPPLICATIONDATA TABLE
BPM_USERAPPLICATIONDATA_GRANT TABLE
WFRULEDICTIONARY TABLE
WFTASKDISPLAY TABLE
WFTASKTIMER TABLE
WFNOTIFICATIONMESSAGES TABLE
WFTASKERROR TABLE
WFHEADERPROPS TABLE
WFCERTAUTHORITY TABLE
WFCERTIFICATEREVOKED TABLE
WFCERTIFICATE TABLE
WFEVIDENCE TABLE
WFTASKASSIGNMENTSTATISTIC TABLE
WFTASKSEQUENCECHANGES TABLE
WFTASKAGGREGATION TABLE
SOAQTZ_SIMPLE_TRIGGERS TABLE
SOAQTZ_CRON_TRIGGERS TABLE
SOAQTZ_BLOB_TRIGGERS TABLE
SOAQTZ_TRIGGER_LISTENERS TABLE
SOAQTZ_CALENDARS TABLE
SOAQTZ_PAUSED_TRIGGER_GRPS TABLE
SOAQTZ_FIRED_TRIGGERS TABLE
SOAQTZ_SCHEDULER_STATE TABLE
SOAQTZ_LOCKS TABLE
MEDIATOR_INSTANCE TABLE
MEDIATOR_CASE_INSTANCE TABLE
MEDIATOR_CASE_DETAIL TABLE
MEDIATOR_CALLBACK TABLE
MEDIATOR_CORRELATION TABLE
MEDIATOR_AUDIT_DOCUMENT TABLE
MEDIATOR_CASE_DETAIL_V VIEW
MEDIATOR_PAYLOAD TABLE
MEDIATOR_DEFERRED_MESSAGE TABLE
MEDIATOR_CONTAINERID_LEASE TABLE
MEDIATOR_GROUP_STATUS TABLE
MEDIATOR_RESEQUENCER_MESSAGE TABLE
SENSOR_SEQUENCE TABLE
ACTIVITY_SENSOR_VALUES TABLE
VARIABLE_SENSOR_VALUES TABLE
FAULT_SENSOR_VALUES TABLE
BPEL_PROCESS_INSTANCES VIEW
BPEL_ACTIVITY_SENSOR_VALUES VIEW
BPEL_VARIABLE_SENSOR_VALUES VIEW
BPEL_VARIABLE_ANALYSIS_REPORT VIEW
BPEL_FAULT_SENSOR_VALUES VIEW
BPM_SEQUENCE TABLE
BPM_PROCESS TABLE
BPM_PROCESS_PARTNER TABLE
BPM_PARTNER_SERVICE TABLE
BPM_ACTIVITY TABLE
BPM_ACTIVITY_PROPERTY TABLE
BPM_PROCESS_PARTNER_SERVICE_V VIEW
BPM_ACTIVITY_INSTANCE TABLE
WI_EXPIRABLE VIEW
WI_EXPIRABLE_CLUSTER VIEW
DS_UNRESOLVED VIEW
DS_RESOLVED VIEW
BRSEQUENCE TABLE
BRDECISIONINSTANCE TABLE
BRDECISIONUNITOFWORK TABLE
BRDECISIONFAULT TABLE
DECISIONCOMPONENTINSTANCEVIEW VIEW
DECISIONCOMPONENTERRORVIEW VIEW
SOAQTZ_JOB_DETAILS TABLE
SOAQTZ_JOB_LISTENERS TABLE
SOAQTZ_TRIGGERS TABLE
EDN_EVENT_SUBSCRIPTIONS TABLE
EDN_RETRY_COUNT TABLE
EDN_CLUSTERS TABLE
EDN_EVENT_ERROR_STORE TABLE
EDN_EVENT_QUEUE_TABLE TABLE
AQ$_EDN_EVENT_QUEUE_TABLE_S TABLE
AQ$_EDN_EVENT_QUEUE_TABLE_T TABLE
AQ$EDN_EVENT_QUEUE_TABLE_S VIEW
AQ$_EDN_EVENT_QUEUE_TABLE_H TABLE
SYS_IOT_OVER_89447 TABLE
AQ$_EDN_EVENT_QUEUE_TABLE_G TABLE
AQ$_EDN_EVENT_QUEUE_TABLE_I TABLE
AQ$_EDN_EVENT_QUEUE_TABLE_F VIEW
AQ$EDN_EVENT_QUEUE_TABLE VIEW
AQ$EDN_EVENT_QUEUE_TABLE_R VIEW
EDN_OAOO_DELIVERY_TABLE TABLE
AQ$_EDN_OAOO_DELIVERY_TABLE_S TABLE
AQ$_EDN_OAOO_DELIVERY_TABLE_T TABLE
AQ$EDN_OAOO_DELIVERY_TABLE_S VIEW
AQ$_EDN_OAOO_DELIVERY_TABLE_H TABLE
SYS_IOT_OVER_89475 TABLE
AQ$_EDN_OAOO_DELIVERY_TABLE_G TABLE
AQ$_EDN_OAOO_DELIVERY_TABLE_I TABLE
AQ$_EDN_OAOO_DELIVERY_TABLE_F VIEW
AQ$EDN_OAOO_DELIVERY_TABLE VIEW
AQ$EDN_OAOO_DELIVERY_TABLE_R VIEW
WFTASKHISTORY_TL TABLE
WFCOMMENTS TABLE
WFMESSAGEATTRIBUTE TABLE
WFATTACHMENT TABLE
WFASSIGNEE TABLE
WFREVIEWER TABLE
WFCOLLECTIONTARGET TABLE
WFROUTINGSLIP TABLE
WFNOTIFICATION TABLE
WFNOTIFICATIONSTATUS TABLE
BPELNOTIFICATION TABLE
BPELNOTIFICATION_FILTERMSGS TABLE
BPELNOTIFICATION_INVALADDRS TABLE
WFTASKMETADATA TABLE
WFATTRIBUTELABELMAP TABLE
WFATTRIBUTELABELUSAGE TABLE
WFUSERVACATION TABLE
BPM_PRESENTATION TABLE
LOAN_CUSTOMER TABLE
PC_TASK TABLE
PC_TASKASSIGNEE TABLE
PC_TASKASSIGNEEHISTORY TABLE
PC_TASKHISTORY TABLE
PC_TASKATTACHMENT TABLE
PC_TASKPAYLOAD TABLE
PC_OWF TABLE
WFTASK TABLE
WFTASK_TL TABLE
WFTASKHISTORY TABLE
JCA_NATIVE_CORRELATION TABLE
VERSION TABLE
ID_RANGE TABLE
UPGRADE_INFO TABLE
CUBE_INSTANCE TABLE
CUBE_SCOPE TABLE
CI_INDEXES TABLE
WORK_ITEM TABLE
WI_FAULT TABLE
HEADERS_PROPERTIES TABLE
AUDIT_TRAIL TABLE
AUDIT_DETAILS TABLE
DLV_MESSAGE TABLE
DLV_SUBSCRIPTION TABLE
DOCUMENT_CI_REF TABLE
DOCUMENT_DLV_MSG_REF TABLE
NATIVE_CORRELATION TABLE
TEST_DEFINITIONS TABLE
TEST_DETAILS TABLE
AG_INSTANCE TABLE
AUDIT_COUNTER TABLE
CLUSTER_MASTER TABLE
CLUSTER_NODE TABLE
ADMIN_LIST_CI VIEW
ADMIN_LIST_CX VIEW
ADMIN_LIST_WI VIEW
ADMIN_LIST_TD VIEW
ADMIN_LIST_TDEF VIEW
DBG_WI VIEW
ACTIVITY_PERF VIEW
INSTANCE_PERF VIEW
BPEL_FAULTS_VW VIEW
BPM_CUBE_AUDITINSTANCE TABLE
BPM_CUBE_LOCK TABLE
BPM_CUBE_PROCESSPERFORMANCE TABLE
BPM_CUBE_TASKPERFORMANCE TABLE
BPM_CUBE_WORKLOAD TABLE
BPM_CUBE_AUDITINST_FLEX01 TABLE
BPM_CUBE_PROCPERF_FLEX01 TABLE
BPM_CUBE_TASKPERF_FLEX01 TABLE
BPM_CUBE_WORKLOAD_FLEX01 TABLE
BPM_CUBE_NAMETAB TABLE
BPM_CUBE_NAMETAB_RANGE TABLE
BPM_CUBE_PROCESS_PARTNER_SVC_V VIEW
BPM_CUBE_ACTIVITY_INSTANCE TABLE
BPM_MEASUREMENT_ACTION_SEQ TABLE
BPM_MEASUREMENT_ACTION_EXCEPS TABLE
BPM_MEASUREMENT_ACTIONS TABLE
BPM_CASE TABLE
BPM_CASE_ACTIVITIES TABLE
BPM_CASE_DATA TABLE
BPM_CASE_ATTRIBUTES TABLE
BPM_PROJECTSHAREDATA_SEQ TABLE
BPM_PROJECTSHAREDATA TABLE
LABEL_VERSION_PCBPEL TABLE
COMPOSITE_INSTANCE TABLE
COMPOSITE_INSTANCE_ASSOC TABLE
COMPONENT_INSTANCE TABLE
REFERENCE_INSTANCE TABLE
BRIDGE_INSTANCE TABLE
COMPOSITE_INSTANCE_FAULT TABLE
REJECTED_MESSAGE TABLE
INSTANCE_PAYLOAD TABLE
COMPOSITE_SENSOR_VALUE TABLE
SEQUENCE TABLE
TEST_INSTANCE TABLE
REJECTED_MSG_NATIVE_PAYLOAD TABLE
XML_DOCUMENT TABLE
ATTACHMENT TABLE
ATTACHMENT_REF TABLE
XML_DOCUMENT_REF TABLE
FILEADAPTER_IN TABLE
FILEADAPTER_MUTEX TABLE
TEMP_CUBE_INSTANCE TABLE
TEMP_DOCUMENT_CI_REF TABLE
TEMP_DOCUMENT_DLV_MSG_REF TABLE
TEMP_XML_DOCUMENT TABLE
COMPOSITE_INSTANCE_PURGE TABLE
REFERENCE_INSTANCE_PURGE TABLE
COMPOSITE_FAULT_PURGE TABLE
REJECTED_MESSAGE_PURGE TABLE
COMPONENT_INSTANCE_PURGE TABLE
TEMP_BRDECISION_INSTANCE TABLE
TEMP_WFTASK_PURGE TABLE
TEMP_MEDIATOR_INSTANCE TABLE
PRUNE_RUNNING_INSTS TABLE
ECID_PURGE TABLE
JOB_FLOW_CONTROL TABLE
B2B_BAM_QTAB TABLE
AQ$_B2B_BAM_QTAB_F VIEW
AQ$B2B_BAM_QTAB VIEW
USRSWAP_CORRELATION TABLE
WL_LLR_ADMINSERVER TABLE



List of tables ,which we frequently use for our daily work around in SOA 11g . 


AUDIT_TRAIL
CUBE_INSTANCE
CUBE_SCOPE
COMPOSITE_INSTANCE
COMPONENT_INSTANCE
MEDIATOR_CALLBACK
MEDIATOR_CORRELATION
BPEL_PROCESS_INSTANCES
BPEL_ACTIVITY_SENSOR_VALUES
BPEL_VARIABLE_SENSOR_VALUES
BPEL_FAULT_SENSOR_VALUES
AUDIT_DETAILS
DLV_MESSAGE
DLV_SUBSCRIPTION
BPEL_FAULTS_VW
BPEL_VARIABLE_ANALYSIS_REPORT
JCA_NATIVE_CORRELATION
VERSION
REFERENCE_INSTANCE
FILEADAPTER_IN
XREF_DATA



CUBE_INSTANCE
The first table that you should know is CUBE_INSTANCE

The most important column that you should know as a BPEL developer is cikey
Significance of cikey
This is the instance ID that you see in your BPEL console.This gets incremented in a sequence with creation of BPEL instances.If you don't know what cikey is then here I go. Each BPEL instance is assigned a unique ID.This ID is visible in the BPEL console. And guess what BPEL console does allow you to filter using instance ID.
This is one of the key columns that you should know to do a lot of stuff.And this key cuts across a lot of tables in the dehydration tables.

Select * from tab; to display the list of tables existed in one schema.