TechExuberance: 2016

Defragment workflow related tables in r12

References-

1. How to Reorganize Workflow Tables? (Doc ID 388672.1)

2. EBS Workflow (WF) Analyzer (Doc ID 1369938.1)

Points to Remember –

Some workflow tables are associated to queues so that it is necessary to use the advance queuing instructions to reorganize them.

For tables other than queue tables, please refer to different notes created by RDBMS team to reorganize tables. This activity depends on the RDBMS version.

Defragment tables in workflow r12

Verify tables are not associated to queues –

SQL> select queue_table from dba_queue_tables

2 where queue_table like '%WF%';

QUEUE_TABLE

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

WF_CONTROL

WF_DEFERRED

WF_DEFERRED_TABLE_M

WF_ERROR

WF_IN

WF_INBOUND_TABLE

WF_JAVA_DEFERRED

WF_JAVA_ERROR

WF_JMS_IN

WF_JMS_JMS_OUT

WF_JMS_OUT

WF_NOTIFICATION_IN

WF_NOTIFICATION_OUT

WF_OUT

WF_OUTBOUND_TABLE

WF_REPLAY_IN

WF_REPLAY_OUT

WF_SMTP_O_1_TABLE

WF_WS_JMS_IN

WF_WS_JMS_OUT

WF_WS_SAMPLE

WF_BPEL_QTAB

Note: We are not going to cover De-fragment of advance queuing tables in this document.

List of workflow related tables that will be defragmented -

'WF_ITEMS',

'WF_ITEM_ACTIVITY_STATUSES',

'WF_ITEM_ATTRIBUTE_VALUES',

'WF_NOTIFICATIONS',

'WF_NOTIFICATION_ATTRIBUTES',

'WF_COMMENTS',

'WF_LOCAL_USER_ROLES'

Shutdown workflow services from frontend

1. Login as sysadmin

· System Administrator

Workflow

2. Stop Workflow Services

Perform shrink table operation

Check for defragmentation on these tables

set lines 1000

col TOTAL_SIZE for a20

select table_name,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE"

from dba_tables

where table_name IN (

'WF_ITEMS',

'WF_ITEM_ACTIVITY_STATUSES',

'WF_ITEM_ATTRIBUTE_VALUES',

'WF_NOTIFICATIONS',

'WF_NOTIFICATION_ATTRIBUTES',

'WF_COMMENTS',

'WF_LOCAL_USER_ROLES'

);

Check for tablespace sizes –

Enable Row movement –

alter table APPLSYS.WF_ITEMS ENABLE ROW MOVEMENT;

alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES ENABLE ROW MOVEMENT;

alter table APPLSYS.WF_ITEM_ATTRIBUTE_VALUES ENABLE ROW MOVEMENT;

alter table APPLSYS.WF_NOTIFICATIONS ENABLE ROW MOVEMENT;

alter table APPLSYS.WF_NOTIFICATION_ATTRIBUTES ENABLE ROW MOVEMENT;

alter table APPLSYS.WF_COMMENTS ENABLE ROW MOVEMENT;

alter table APPLSYS.WF_LOCAL_USER_ROLES ENABLE ROW MOVEMENT;

Shrink space with compact –

alter table APPLSYS.WF_ITEMS SHRINK SPACE COMPACT;

alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES SHRINK SPACE COMPACT;

alter table APPLSYS.WF_ITEM_ATTRIBUTE_VALUES SHRINK SPACE COMPACT;

alter table APPLSYS.WF_NOTIFICATIONS SHRINK SPACE COMPACT;

alter table APPLSYS.WF_NOTIFICATION_ATTRIBUTES SHRINK SPACE COMPACT;

alter table APPLSYS.WF_COMMENTS SHRINK SPACE COMPACT;

alter table APPLSYS.WF_LOCAL_USER_ROLES SHRINK SPACE COMPACT;

Shrink space –

alter table APPLSYS.WF_ITEMS shrink space;

alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES shrink space;

alter table APPLSYS.WF_ITEM_ATTRIBUTE_VALUES shrink space;

alter table APPLSYS.WF_NOTIFICATIONS shrink space;

alter table APPLSYS.WF_NOTIFICATION_ATTRIBUTES shrink space;

alter table APPLSYS.WF_COMMENTS shrink space;

alter table APPLSYS.WF_LOCAL_USER_ROLES shrink space;

Disable row movement –

alter table APPLSYS.WF_ITEMS disable ROW MOVEMENT;

alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES disable ROW MOVEMENT;

alter table APPLSYS.WF_ITEM_ATTRIBUTE_VALUES disable ROW MOVEMENT;

alter table APPLSYS.WF_NOTIFICATIONS disable ROW MOVEMENT;

alter table APPLSYS.WF_NOTIFICATION_ATTRIBUTES disable ROW MOVEMENT;

alter table APPLSYS.WF_COMMENTS disable ROW MOVEMENT;

alter table APPLSYS.WF_LOCAL_USER_ROLES disable ROW MOVEMENT;

Gather fresh statistics on these tables –

exec fnd_stats.gather_table_stats(OWNNAME=>'APPLSYS',tabname =>'WF_ITEMS',percent=>30,CASCADE=>TRUE,degree=>4);

exec fnd_stats.gather_table_stats(OWNNAME=>'APPLSYS',tabname =>'WF_ITEM_ACTIVITY_STATUSES',percent=>30,CASCADE=>TRUE,degree=>4);

exec fnd_stats.gather_table_stats(OWNNAME=>'APPLSYS',tabname =>'WF_ITEM_ATTRIBUTE_VALUES',percent=>30,CASCADE=>TRUE,degree=>4);

exec fnd_stats.gather_table_stats(OWNNAME=>'APPLSYS',tabname =>'WF_NOTIFICATIONS ',percent=>30,CASCADE=>TRUE,degree=>4);

exec fnd_stats.gather_table_stats(OWNNAME=>'APPLSYS',tabname =>'WF_NOTIFICATION_ATTRIBUTES',percent=>30,CASCADE=>TRUE,degree=>4);

exec fnd_stats.gather_table_stats(OWNNAME=>'APPLSYS',tabname =>'WF_ITEMS',percent=>30,CASCADE=>TRUE,degree=>4);

exec fnd_stats.gather_table_stats(OWNNAME=>'APPLSYS',tabname =>'WF_LOCAL_USER_ROLES',percent=>30,CASCADE=>TRUE,degree=>4);

Recheck table and tablespace sizes

Start workflow services from frontend

Try sending a test mail

Check invalid object count and run utlrp

@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects where status='INVALID';