This is Short post about what to do when your Oracle workflow STUCK at Deferred activity. Please Note that in a workflow we add Deferred activities because we want Workflow background engine to Process thise records.
Most of the time we Add this activity in the flow when we don't required any Online progress , or when we want to process data in Batch Mode.Workflow Background Processwill process any workflow activity that is in Deferred Statusand progress it to next activity .
But Many a time we notice that though Workflow background process is scheduled n running, but data in the Deferred queue is still same or keep Increasing.
I recommend that in such a case check count(*) on wf_item_activity_statuses for activity_status ='DEFERRED' , and verify if Count(*) value is going dowm or NOT.
If Not the best solution is to Rebuild the DEFERRED QUEUE ( Workflow has some APIs to do so). While rebuilding the Deferred queue is little time consuming and may required data backup , if you want to Push some CRITICAL records from DEFERRED activity , try to SKIP/RETRY the workflow activity that is STUCK in Deferred status..
SKIP the activities like WAIT/DEFERRED , but if some activity in deferred status and has a PL/SQL Code assocaited with it , in that case you should try RETRY and not to SKIP that.
that's for Now.