List of all states from COMPOSITE_INSTANCE, CUBE_INSTANCE, DLV_MESSAGE tables
- by Deepak Arora
In many of my engagements I 
get asked repeatedly about the states of the composites in 11g and how 
to decipher them, especially when we are troubleshooting issues around 
purging. I have compiled a list of all the states from the 
COMPOSITE_INSTANCE, CUBE_INSTANCE, DLV_MESSAGE and MEDIATOR_INSTANCE tables. These are the
 primary tables that are used when using BPEL composites and how they 
are used with the ECID. 
 
   Composite State Values  COMPOSITE_INSTANCE States  
   
     
       
        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 
       
       
        64 
        - 
       
       
         
         
       
     
   
   
 
  Normal
  0
  
  
  
  
  false
  false
  false
  
  EN-CA
  X-NONE
  X-NONE
  
   
   
   
   
   
   
   
   
   
   
   
  
  MicrosoftInternetExplorer4
  
   
   
   
   
   
   
   
   
   
   
   
  
 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
 
 /* Style Definitions */
 table.MsoNormalTable
	{mso-style-name:"Table Normal";
	mso-tstyle-rowband-size:0;
	mso-tstyle-colband-size:0;
	mso-style-noshow:yes;
	mso-style-priority:99;
	mso-style-qformat:yes;
	mso-style-parent:"";
	mso-padding-alt:0in 5.4pt 0in 5.4pt;
	mso-para-margin:0in;
	mso-para-margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";
	mso-ascii-font-family:Calibri;
	mso-ascii-theme-font:minor-latin;
	mso-fareast-font-family:"Times New Roman";
	mso-fareast-theme-font:minor-fareast;
	mso-hansi-font-family:Calibri;
	mso-hansi-theme-font:minor-latin;
	mso-bidi-font-family:"Times New Roman";
	mso-bidi-theme-font:minor-bidi;}
Any value in the range of
32 to 63 indicates that the composite instance state has not been enabled, but
the instance state is updated for faults, aborts, etc. CUBE_INSTANCE States  
   
     
       
        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 
       
     
   DLV_MESSAGE States 
   
     
       
        State 
        Description 
       
       
        0 
        STATE_UNRESOLVED 
       
       
        1 
        STATE_RESOLVED 
       
       
        2 
        STATE_HANDLED 
       
       
        3 
        STATE_CANCELLED 
       
       
        4 
        STATE_MAX_RECOVERED 
       
     
   
   Since now in 11g the 
Invoke_Messages table is not there so to distinguish between a new 
message (Invoke) and callback (DLV) and there is DLV_TYPE column that 
defines the type of message: 
   DLV_TYPE States  
   
     
       
         
           
             
               
                 
                   
                     
                       
                         
                           
                             
                               
                                 
                                   
                                     
                                      State 
                                      Description 
                                     
                                     
                                      1 
                                      Invoke Message 
                                     
                                     
                                      2 
                                      DLV Message 
                                     
                                   
                                 
                                    
                                MEDIATOR_INSTANCE 
                                 
                                 
                                   
                                     
                                       STATE 
                                       Description 
                                     
                                     
                                       0 
                                       No faults but there still might be running instances 
                                     
                                     
                                       1 
                                       At least one case is aborted by user 
                                     
                                     
                                       2 
                                       At least one case is faulted (non-recoverable) 
                                     
                                     
                                       3 
                                       At least one case is faulted and one case is aborted 
                                     
                                     
                                       4 
                                       At least one case is in recovery required state 
                                     
                                     
                                       5 
                                       At least one case is in recovery required state and at least one is aborted 
                                     
                                     
                                       6 
                                       At least one case is in recovery required state and at least one is faulted 
                                     
                                     
                                       7 
                                       At least one case is in recovery required state, one faulted and one aborted 
                                     
                                     
                                       >=8 and < 16  
                                       Running 
                                     
                                     
                                      >= 16   
                                       Stale 
                                     
                                   
                                 
                                  
                               
                             
                           
                         
                       
                     
                   
                 
               
             
           
         
         
       
     
   
  In my next blog posting I will walk through the lifecycle of a BPEL process using the above states for the following use cases: 
  - New BPEL process - initial Receive activity 
  - Callback BPEL process - mid-level Receive activity 
  As always comments and questions welcome! 
  Deepak