Even if you do not use workflow RAC affinity, you can still take advantage of partitioning for Oracle Workflow tables. Partitioning addresses key issues in supporting very large tables and indexes by dividing 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.
You can optionally run a script called wfpart.sql to partition certain Oracle Workflow tables that store runtime status data. This step is highly recommended for performance gain.
Note: If you implemented workflow RAC affinity, then you already partitioned your Oracle Workflow tables as part of the RAC affinity setup. In this case you do not need to run the wfpart.sql script. See: Setting Up Workflow RAC Affinity.
The script partitions four Workflow tables and recreates the associated indexes. The tables are partitioned by item type and hash subpartitioned by item key. The following table shows the Workflow tables and indexes on which the script runs.
Partitioned Tables and Associated Indexes
| Table | Indexes |
|---|---|
| WF_ITEM_ACTIVITY_STATUSES | WF_ITEM_ACTIVITY_STATUSES_PK, WF_ITEM_ACTIVITY_STATUSES_N1, and WF_ITEM_ACTIVITY_STATUSES_N2 |
| WF_ITEM_ACTIVITY_STATUSES_H | WF_ITEM_ACTIVITY_STATUSES_H_N1 and 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, and 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.
Important: If you are running the partitioning script through Oracle Net Services, then you must set the TWO_TASK variable before you begin.
The wfpart.sql script is located in the $FND_TOP/patch/115/sql directory. Use the script as follows:
sqlplus <apps_user> @wfpart <fnd_user> <fnd_password> <apps_user> <apps_password> <utl_dir_location> Enter password: <apps_password>
Replace <apps_user> with the user name for the APPS user. The user name is usually apps. Replace <fnd_user> and <fnd_password> with the user name and password for the user that connects to Oracle Application Object Library data in Oracle E-Business Suite. The user name is usually applsys. Replace <apps_user> and <apps_password> with the user name and password for the APPS user. Replace <utl_dir_location> with a directory that is included in the UTL_FILE_DIR database initialization parameter. Then enter the password for the APPS user at the prompt.
For example:
sqlplus apps @wfpart applsys <fnd_password> apps <apps_password> /usr/tmp Enter password: <apps_password>
The script writes a new script, also named wfpart.sql, to the specified directory. You can optionally edit this second wfpart.sql script to customize it. Then run this script to perform the partitioning. Logging is turned on for this script by default. If your database setup permits, you can optionally turn off logging to increase the performance of the script.