Home » Oracle » Oracle Ebuisness Suite » How to rebuild WF_DEFERRED_TABLE_M Queue

How to rebuild WF_DEFERRED_TABLE_M Queue

Sometimes we need to rebuild the WF_DEFERRED_TABLE_M queue due to corruption. Here are the steps to perform it.

(1) First, take a pre snapshot of all the objects related to that queue

Login to server and source the EBS environment

spool WF_DEFERRED_TABLE_M_objects_pre.lst
select object_name, object_type, status, owner
from dba_objects
where object_name like '%WF_DEFERRED_TABLE_M%';
spool off

(2) Now we will proceed with the steps to rebuild the queue

a) Please stop the workflow services and the workflow background engine programs. Please make sure no workflow programs are running
i.e Concurrent Manager should be down

cd $ADMIN_SCRIPTS_HOME
adcmctl.sh stop apps/appspass


b)  Create a manual backup of the queue table

sqlplus apps/appspass
create table wf_deferred_table_bk as select * from WF_DEFERRED_TABLE_M;
select count(*) from WF_DEFERRED_TABLE_M;

c) Drop the queue using the following. Please make sure you use uppercase for the APPLSYS APPS:

sqlplus apps/appspass @wfqued.sql APPLSYS appspass

d) Recreate the queue using the following:

sqlplus apps/appspass @wfquec2.sql APPS APPLSYS appspass

e)Recreate the index:

sqlplus apps/appspass @wfqidxc2.sql APPLSYS appspass APPLSYSX

f) Make sure the histograms are added back:

sqlplus apps/appspass @wfhistc.sql APPLSYS

g) Re-populate WF_DEFERRED_TABLE_M. This may take a long time and make sure the Workflow Background Engine is not running::

sqlplus apps/appspass @wfbkgbld.sql APPLSYS

This will take some time like 1 hour

h) Verify


select count(*) from WF_DEFERRED_TABLE_M;


It should have the same rows as earlier

(3) Now take the post snapshot of all the queue related objects

spool WF_DEFERRED_TABLE_M_objects_post.lst
select object_name, object_type, status, owner
from dba_objects
where object_name like '%WF_DEFERRED_TABLE_M%';
spool off

(4) Please compare pre and post list to make sure everything is alright

See also  Handling of seed data in R12.2 online patching

(5) Compile all the invalid objects with the adadmin

Related Articles

SQL to check the AQ setup and Workflow Agent Listener/WF Java Deferred Agent Listeners are running
Troubleshooting Workflow Notification Mailer issues
how to rebuild the wf_java_deferred queue
Top Questions about JAVA/JRE in Oracle Apps
Workflow Notification Mailer Configuration in Oracle Apps R12

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top