Converting from a Non-Partitioned Database to a Partitioned Database

Execute $AF_HOME/db/oms/nonpartition-to-partition-migration.sql to convert your non-partitioned database to a partitioned database.

Prerequisites

  1. The TIBCO Fulfillment Order Management 3.0.2 database is non-partitioned.
  2. Execute privileges on the DBMS_REDEFINITION package (granted to EXECUTE_CATALOG_ROLE)
  3. User running the script requires the following privileges:
    • CREATE ANY TABLE
    • ALTER ANY TABLE
    • DROP ANY TABLE
    • LOCK ANY TABLE
    • SELECT ANY TABLE
    • CREATE ANY TRIGGER
    • CREATE ANY INDEX

OR database admin privileges.

Procedure

  • Go to the $AF_HOME/db/oms directory and execute the nonpartition-to-partition-migration.sql script as: 
    SQL> @nonpartition-to-partition-migration.sql <user name> <table space name>

Result

After running the script, slight modification is done to the existing table relationships to create a hierarchy tree in the OMS database where the ORDERS table is now a parent of all the order related tables. The ORDERS table is RANGE partitioned on the basis of the "partition date" column. This is a new column introduced as part of partition migration. This column has the same value of the submitted date and is populated upon order submission.

The following image shows how the OMS tables are related to each other. For example, the child relationship between ORDERS and ORDERS_AMENDMENT signifies foreign key constraint from the ORDERS to ORDERS_AMENDMENT table.

With a partitioned database, whenever a database purge is needed, the database admin can perform a "drop partition" activity to clean up the database. Dropping partitions from the ORDERS table cascades the drop partition event to all its child tables and removes all the order related data from all the tables for that partition.

For more information, see "Database Partitioning" in the TIBCO Fulfillment Order Management Administration Guide.