Logfile locations in EBS r12.1 and EBS r12.2

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

• Define Profile Options
• Oracle Applications Manager
• 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';