Setup Steps

Step 1 Partitioning Workflow Tables

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, once partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. In this way, partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

The script partitions four Workflow tables and recreates the associated indexes. The following table shows the Workflow tables and indexes on which the script runs.

Table

Index

WF_ITEM_ACTIVITY_STATUSES

WF_ITEM_ACTIVITY_STATUSES_PK

WF_ITEM_ACTIVITY_STATUSES_N1

WF_ITEM_ACTIVITY_STATUSES_N2

WF_ITEM_ACTIVITY_STATUSES_H

WF_ITEM_ACTIVITY_STATUSES_H_N1

WF_ITEM_ACTIVITY_STATUSES_H_N2

WF_ITEM_ATTRIBUTE_VALUES

WF_ITEM_ATTRIBUTE_VALUES_PK

WF_ITEMS

WF_ITEMS_PK

WF_ITEMS_N1

WF_ITEMS_N2

WF_ITEMS_N3

Before running the partitioning script, you should back up these four tables so that you can restore them in case the script fails.

To run the script, you must have sufficient free space on the table and index tablespaces. During the creation of the partitioned tables, the script requires slightly more diskspace than the underlying tables, in the same tablespace where the underlying tables are located. Similarly, sufficient free space is required for the index tablespace.

Additionally, you should allow sufficient time for the script to run. The amount of time needed depends on the amount of data in the tables. When the tables already contain existing data, such as after an upgrade from a previous release, the script requires more time than it does when the tables are empty, such as after a fresh installation of Oracle Workflow. To minimize the time required, run the script as early as possible in your setup process.

Attention: If you are running the partitioning script through Oracle Net Services, then you must set the TWO_TASK variable before you begin.

For Oracle Workflow embedded in Oracle Applications, the wfupartb.sql script is located in the admin/sql subdirectory under $FND_TOP. Use the script as follows:

sqlplus <apps_user>/<apps_passwd> @wfupartb <fnd_user>

<fnd_passwd> <apps_user> <apps_passwd>

For example:

sqlplus apps/apps @wfupartb applsys apps apps apps

For standalone Oracle Workflow, the wfupart.sql script is located in the wf/admin/sql subdirectory in your Oracle Home. Use the script as follows:

sqlplus <wf_user>/<wf_passwd> @wfupart <wf_user> <wf_passwd>

For example:

sqlplus owf_mgr/owf_mgr @wfupart owf_mgr owf_mgr

If the partitioning script fails, you must perform any necessary cleanup manually. Since the script's operations are DDL operations running in nologging mode, rollback is not possible.