Purge Obsolete oracle workflow Logs and keeping the logs for one or two years.

I will discuss today how i purged successfully fixed asset workflow logs and kept the latest 2 years logs only.to do this you have to run the concurrent request "Purge Obsolete Workflow Runtime Data”. The concurrent request takes some parameters in order to delete the fixed assets logs.

To proceed further enter the parameters below and leave all others blank:

Item Type FA Account Generator
Item Key - leave blank
Age -730 -- which is the age in day

persistence type Permanent

To determine the persistence type use the below query:

SQL> select name,persistence_type,persistence_days from wf_item_types where name ='FAFLEXWF';

NAME     PERSISTE PERSISTENCE_DAYS

-------- -------- ----------------

FAFLEXWF PERM

We can see that this type is permanent so we choose permanent not temporary.

I choose commit every 5000 transaction because i want not to overload the undo segment.

Then submit the request as shown in the attached screen.

What if you want to purge all the permanent of temporary workflow and keep only the past 1 year logs then you will change the parameters to be as shown in the  below screen:

The following tables are affected by Purge Obsolete Workflow Runtime Data
FNDWFPR concurrent request:

WF_ITEM_ACTIVITY_STATUSES
WF_COMMENTS
WF_ITEMS
WF_ITEM_ACTIVITY_STATUSES_H 
WF_ITEM_ATTRIBUTE_VALUES
WF_ACTIVITY_TRANSITIONS 
WF_ACTIVITY_ATTR_VALUES
WF_PROCESS_ACTIVITIES
WF_ACTIVITY_ATTRIBUTES_TL
WF_ACTIVITY_ATTRIBUTES
WF_NOTIFICATIONS 
WF_ACTIVITIES
WF_ACTIVITIES_TL
WF_NOTIFICATION_ATTRIBUTES
WF_LOCAL_USER_ROLES
WF_LOCAL_ROLES
WF_USER_ROLE_ASSIGNMENTS