[Oracle SOA 12c] Principles SOA Dehydration stores – Useful SQL Queries

LinkedIn 0
Twitter
Facebook 0
Google+ 0

I share my main returns as part of a client request to extract the payloads of the instances in a given time interval, with payloads of instances in CLOB format. It’s an opportunity to explore the main SOA dehydration stores and queries the most used in 12c, and share them with you for such requests, I hope it will be useful 🙂

 

I. Description  of the principal’s tables  used

Below mentioned tables are the important ones that will be used to locate the instances.

CUBE_INSTANCE
COMPOSITE_INSTANCE
SCA_SENSOR_VALUE

 

COMPOSITE_INSTANCE

Below are the important columns in composite instance tables.

                           ECID: Unique values for one end-to-end transactions across several tables.
                           ID: Instance ID that is visible in Enterprise Manager Console
                           PARENT_ID: This will be null for initiator composite and child composite will have the value of parent composite
                           COMPOSITE_DN: Composite name along with partition name
                           STATE: State of the instance like completed, running etc which is explained in details
                           CREATED_TIME: Creation time of an instance

List of states and its description:

State Description
0 Running
1 Completed
2 Running with faults
3 Completed with faults
4 Running with recovery required
5 Completed with recovery required
6 Running with faults and recovery required
7 Completed with faults and recovery required
8 Running with suspended
9 Completed with suspended
10 Running with faults and suspended
11 Completed with faults and suspended
12 Running with recovery required and suspended
13 Completed with recovery required and suspended
14 Running with faults, recovery required and suspended
15 Completed with faults, recovery required and suspended
16 Running with terminated
17 Completed with terminated
18 Running with faults and terminated
19 Completed with faults and terminated
20 Running with recovery required and terminated
21 Completed with recovery required and terminated
22 Running with faults, recovery required and terminated
23 Completed with faults, recovery required and terminated
24 Running with suspended and terminated
25 Completed with suspended and terminated
26 Running with faulted, suspended, and terminated
27 Completed with faulted, suspended, and terminated
28 Running with recovery required, suspended, and terminated
29 Completed with recovery required, suspended, and terminated
30 Running with faulted, recovery required, suspended, and terminated
31 Completed with faulted, recovery required, suspended, and terminated
32 Unknown

 

CUBE_INSTANCE

Below are the important columns in cube_instance tables.

   CIKEY: Unique value for an instance
   CREATION_DATE: Creation date
   STATE: State of Instance
   STATUS: Status of Instance
   ECID: Unique hexadecimal id for the end to end transaction
   COMPOSITE_NAME: Name of composite
   DOMAIN_NAME: Partition Name
   COMPONENT_NAME: Name of component
   COMPOSITE_REVISION: Revision Number of composites

List of states and its description:
State Description
0 STATE_INITIATED
1 STATE_OPEN_RUNNING
2 STATE_OPEN_SUSPENDED
3 STATE_OPEN_FAULTED
4 STATE_CLOSED_PENDING_CANCEL
5 STATE_CLOSED_COMPLETED
6 STATE_CLOSED_FAULTED
7 STATE_CLOSED_CANCELLED
8 STATE_CLOSED_ABORTED
9 STATE_CLOSED_STALE
10 STATE_CLOSED_ROLLED_BACK

SCA_SENSOR_VALUE

Below are the important columns in composite instance tables.
                                  COMPOSITE_ID: Instance ID that is visible in EM console or Value of ID in the composite_instance                                                                                                        table
                                  COMPONENT_NAME: Name of the component
                                  SENSOR_NAME: Name of the sensors
                                  PARTITION_DATE: Creation date
                                  STRING_VALUE: Sensor value in String datatype
                                  NUMBER_VALUE: Sensor value in Number datatype
                                  DATE_VALUE: Sensor value in Date datatype
                                  CLOB_VALUE: Sensor value in CLOB datatype
                                  BLOB_VALUE: Sensor value in Blob datatype
Other Tables of SOA  dehydration stores, that we use to extract the details of instances, like:
  • DLV_MESSAGE
 Stores incoming (invocation) and callback messages upon receipt. This table only stores the metadata for a message (for example, current state, process identifier, and receive date).
  • XML_DOCUMENT

Stores all large objects in the system (for example, dlv_message documents). This table stores the data as binary large objects (BLOBs). Separating the document storage from the metadata enables the metadata to change frequently without being impacted by the size of the documents.

  • AUDIT_TRAIL

Stores the audit trail for instances. The audit trail viewed in Oracle BPEL Control is created from an XML document. As an instance is processed, each activity writes events to the audit trail as XML.

 

II. Some Useful Queries

## Query to get the count of instances on different state  ##

 

## If you want to get the same details on composite wise   ##

 

## Query to find Payload of a composite  ##

 

## Query to  display instance id of all composites ##

 

##  To  delete  instances   not in Recovery  or  Running  state  ##

 

## To  extract   the CLOB  of the Payload  for the  instance in the suspending canceled state   ##

 

## To  extract the failed  instances  for the ‘composite_name’ ##

NOTE. For  DLV_MESSAGE :  Failed State is the value ‘0’  and  Completed  state is the value ‘2’

RQE. While running queries in Production SOA infra schema, make sure you restrict the number of records to search by adding date filter else it may throw temp tablespace error. 

 

III.  For more information

 

Bonne lecture !

 

 

LinkedIn 0
Twitter
Facebook 0
Google+ 0

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *