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
(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