Steps to reorganize tables and their corresponding indexes

1. The concurrent request “Purge Obsolete Workflow Runtime Data” has been completed successfully of request id # 3416008

2. Stopped the applications services in both the nodes

3. The following tables were reorganized in their tablespaces APPS_TS_TX_DATA

Command to move the tables:

select 'alter table ' owner '.' table_name ' move tablespace ' tablespace_name ';' from dba_tables where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

a. WF_ITEM_ATTRIBUTE_VALUES
b. WF_ITEM_ACTIVITY_STATUSES
c. WF_NOTIFICATION_ATTRIBUTES

4. The following corresponding indexes were rebuilded in their tablespace APPS_TS_TX_IDX,

a .WF_ITEM_ACTIVITY_STATUSES_N1
b. WF_ITEM_ACTIVITY_STATUSES_N3
c. WF_ITEM_ACTIVITY_STATUSES_PK
d. WF_ITEM_ACTIVITY_STATUSES_N2
e. WF_ITEM_ACTIVITY_STATUSES_N4
f. WF_ITEM_ATTRIBUTE_VALUES_PK
g. WF_NOTIFICATIONS_ATTR_PK

Command to rebuild the indexes in the reorganized tables:

select 'alter index ' owner '.' index_name ' rebuild tablespace ' tablespace_name ';' from dba_indexes where index_type='NORMAL' and table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

5. Checked the status of the indexes using the following command,

select status from dba_indexes where index_type='NORMAL' and table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

6. Startup the application services in both nodes

7. The concurrent program “Gather Table Statistics” to analyze the tables has been ran

8. Monitoring the performance.