Skip Headers
Oracle® Communications Billing and Revenue Management System Administrator's Guide
Release 7.5

E16719-13
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

25 Partitioning Database Tables

This chapter explains how to organize your Oracle Communications Billing and Revenue Management (BRM) database by using partitioned tables.

Before partitioning any tables, you should be familiar with the following:

About Partitioning

Partitioning is an optional feature that splits tables and indexes into smaller, more manageable units. When you no longer need the data stored in a partition, you can purge it from your database by deleting the partition (see "About Purging Data").

To use this feature, you need the following:

  • An Oracle database. Partitioning is available only for Oracle databases.

  • Oracle Partitioning. You license this third-party software from Oracle.

To enable partitioning for BRM, do one of the following:

In either case, you should ensure that the $ENABLE_PARTITION parameter is set to yes ($ENABLE_PARTITION = yes;) in the pin_setup.values file. The above operations should set this parameter for you. In addition, the tables for the /event class are automatically enabled when you run pin_setup if the $ENABLE_PARTITION parameter is set to yes.

If you wish to partition non-event tables, assign a list of the classes (not including /event) that you wish to partition to the @CLASSES_TO_BE_PARTITIONED parameter in the pin_setup.values file before you run the pin_setup utility.

For example:

@CLASSES_TO_BE_PARTITIONED = ("/journal:local","/account:local");

Note:

You should list only base classes.

The :local and :global suffixes designate two types of indexes that are available for non-event partitions (all event object tables are local):

:global indexes

  • Advantage: Search operations become fast.

  • Disadvantage: BRM services must be shut down before adding or dropping partitions because these indexes must be rebuilt.

:local indexes

  • Advantage: Maintenance can be done without shutting down BRM.

  • Disadvantage: Performance may suffer. Search operations will be more time consuming because searches must hit every local index.

When you use partitioning, objects are stored in a partition according to criteria set when the partitions are created:

  • Event objects that should never be purged (non-purgeable events) are stored in partition_historic. See "About the Default Partitioning Scheme".

  • All other objects are stored in date-based, purgeable partitions according to the date they were created. For example, if a partition includes a date range from January 15 to February 15, all objects that have a creation date (PIN_FLD_CREATED_T) in that range are included.

    You create separate partitions for real-time events and delayed events. Real-time events are created by real-time rating; delayed events are created by pipeline batch rating. You can purge real-time events, delayed events, or both.

    Note:

    Delayed partitioning is not supported for non-event classes.
  • If there is no date-based partition, objects are stored in partition_last. See "About Objects Stored in partition_last and partition_last_pin".

For partitioned event tables, you can use the default purgeable partitions that pin_setup creates. If you do not want to use the default partitions, you can create custom purgeable partitions with the partition_utils utility. For non-event tables, you must explicitly create custom purgeable partitions.

See "About Partitioning Schemes" and "Customizing Partition Limitations".

Note:

A partition applies to multiple object tables based on the base class. When you remove a partition, it removes partitions in all the partitioned tables for that base class and its subclasses.

To create partitions and purge data, use the partition_utils utility. See "About Managing Partitions".

Note:

Rule-based optimization is not available for partitioned tables and indexes. For more information, see "Using Rule-Based Optimization versus Cost-Based Optimization" in BRM Installation Guide.

Partitioning Overview

The flow chart in Figure 25-1 provides an overview of how to set up and use partitioning. All of the steps before you customize or add partitions must be done when you install BRM.

Important:

To enable partitioning when you install BRM, you configure and run pin_setup.

For an overview of partitioning enabled by upgrading, see "About Upgrading from a Nonpartitioned to a Partitioned Database".

Figure 25-1 Setup and Use of Partitioning

Description of Figure 25-1 follows
Description of "Figure 25-1 Setup and Use of Partitioning"

About Partitioning Schemes

Partitions are based on dates; your partitioning scheme determines how the date-based time periods are divided. Partitions can be daily, weekly, or monthly. You can specify multiples of each time period; for example, you can create partitions based on five days or on three weeks.

Determining the appropriate partition size depends on many factors. Generally speaking, partitions should not exceed 120,000,000 entries for standard BRM configurations. For more specific information, see the Oracle documentation or contact Oracle technical support.

You can set up partitioning schemes based on different time periods in advance; for example, you can specify three monthly partitions followed by three weekly partitions. However, every table of the same base class must have the same partitioning scheme; you cannot create different partitions for different tables with the same base class.

Important:

When you add partitions, you define a start date for the first partition. The start date cannot be earlier than the day after tomorrow. For example, if the current date is January 1, the earliest start date for the new partition is January 3.

If you use pipeline batch rating, Rated Event (RE) Loader loads delayed events into the BRM database. You can enable partitioning for delayed events and create partitions for the delayed events. See "About Partitions for Delayed Events".

Note:

All indexes on event tables are local. When you partition an event table, the associated index is identically partitioned by the Oracle database.

About Nonpurgeable Events

By default, certain classes of events cannot be purged from the database. All event objects belonging to these classes are automatically stored in partition_historic. You cannot remove this partition.

For the default list of events stored in partition_historic, see "Event Objects Stored in partition_historic".

To customize this list, see "Customizing the List of Events Stored in partition_historic".

About Objects Stored in partition_last and partition_last_pin

The partition_last partition holds objects that occur after the time period covered by the regular partitions if no purgeable partitions are available for such objects. You cannot remove this partition for real-time event tables or non-event tables.

Caution:

  • This partition is not intended to store objects; it is a temporary container for them. To keep objects out of these partitions, make sure there are partitions to hold new events. See "Adding Partitions".

  • To purge objects stored in spillover partitions, stop all delayed-event loading and add partitions and use the -f (force) parameter. Adding partitions in this way moves the data from the spillover partitions to the added partitions, but it takes much longer than adding partitions normally.

If your partitions are enabled for delayed events, partition_last is used for delayed events; partition_last_pin is used for real-time events.

About the Default Partitioning Scheme

If you enable partitioning when you install BRM and choose to create the default partitions, your real-time events are stored in the following partitions:

  • partition_historic (event tables only)

  • partition_last

  • 12 monthly partitions

  • +1 partition for objects created on the start date

Each monthly partition stores objects created on the date of the month you install BRM through the previous date of the next month. For example, if you install BRM on January 15, the partitions cover these time periods: January 15-February 14, February 15-March 14, and so on.

Note:

The naming convention for partitions is P_R_MMDDYYYY (real time) and P_D_MMDDYYYY (delayed) based on the start date.

If You Did Not Create Default Partitions

If you enable partitioning when you install BRM but choose not to create the default partitions, your real-time object tables are divided into fewer partitions: partition_historic (stores all non-purgeable events) and partition_last (stores all purgeable objects). This is sufficient for a test or development system in which purging by partition is not required. For a production system, however, you must add more purgeable partitions. See "Adding Partitions".

Upgrade Partitioning Scheme

If you enable partitioning by upgrading your database after you install BRM, your partitioned real-time tables are divided into three partitions:

  • partition_migrate: Holds all event objects created before the nonpartitioning-to-partitioning upgrade.

  • partition_historic: Holds nonpurgeable events created after the nonpartitioning-to-partitioning upgrade. Nonpurgeable events should not be purged from the database. See "Event Objects Stored in partition_historic".

  • partition_last: A spillover partition that is not intended to store event objects you want to purge or preserve. If you do not add purgeable partitions to your event tables before BRM resumes generating events, purgeable events created after the upgrade are stored in this partition.

About Partitions for Delayed Events

To create partitions for delayed events, you use the partition_utils utility to do the following:

  • Enable delayed-event partitions. You specify which events you want partitions for.

  • Add partitions for delayed events. You add delayed partitions the same way you add real-time partitions.

    Note:

    When you enable partitions, the partition_utils utility automatically runs the update operation. This aligns the delayed partitions with any partitions already defined in the EVENT_T table. Therefore, you do not need to add partitions for delayed events after enabling them.

When you add partitions for delayed events, the event tables are divided into the default partitions plus the following partitions:

  • Partitions for delayed events: The time periods covered by these partitions do not need to match the periods covered by the partitions for real-time event objects. For example, you can use daily partitions for real-time events and weekly partitions for delayed events.

  • partition_last_pin: When delayed-event partitions are added to tables, this partition is added for real-time event objects; partition_last becomes the spillover partition for delayed events for which there is no other partition.

    Caution:

    Spillover partitions are not intended to store event objects you want to purge or preserve. To keep objects out of these partitions, you need to add partitions for upcoming dates (see "Adding Partitions"). To purge events stored in spillover partitions, you must add partitions and use the -f (force) parameter. When you do that, you must stop all BRM processes and stop all delayed-event loading. Adding partitions moves the data from the spillover partitions, but it takes much longer than normally adding partitions.

Delayed-event storage is based on the date that the delayed events are loaded into the BRM database.

If you enable delayed-event partitions for an event class, but then decide you do not need partitions for that delayed-event class, you can use sqlplus to remove delayed-event partitions for that event table, then remove the partition_last_pin partition for that event table. See "Disabling Delayed-Event Partitioning".

Overview of Partition Schemes

The following figures show the possible partition schemes.

Table 25-1 No Partitions

Table

Object table


If you do not enable partitions when installing BRM, every object table has no partitions.

Table 25-2 Real-Time Partitioning Enabled, No Default Partitions

Partition Partition

partition_historic

(event tables only)

partition_last


An object table designated for partitioning is partitioned as shown in Table 25-2 if you enable partitions but do not create the 12 default partitions.

Caution:

Do not use the partition scheme shown in Table 25-2 in a production system because all non-historic real-time objects are stored in partition_last. You need to add real-time partitions.

Table 25-3 Real-Time Partitioning Enabled, 12 Default Partitions

Partition Partition Partition Partition Partition Partition Partition

partition_historic

(event tables only)

Real Time Partition 1

Real Time Partition 2

Real Time Partitions 3 through 10

Real Time Partition 11

Real Time Partition 12

partition_last


An object table designated for partitioning is partitioned as shown in Table 25-3 if you enable partitions and accept the 12 default partitions.

Table 25-4 Real-Time Partitioning Enabled, Custom Default Partitions

Partition Partition Partition Partition Partition Partition Partition

partition_historic

(event tables only)

Real Time Partition 1

Real Time Partition 2

Real Time Partition 3

Real Time Partitions 4 through N - 1

Real Time Partition N

partition_last


An object table designated for partitioning is partitioned as shown in Table 25-4 if you enable partitions and create custom partitions.

Table 25-5 Real-Time Partitioning Enabled, Delayed Partitioning Enabled, No Default Partitions

Partition Partition Partition

partition_historic

partition_last_pin

partition_last


The event table is partitioned as shown in Table 25-5 if you enable partitions without the 12 default partitions and then enable delayed-event partitions.

Caution:

Do not use the partition scheme shown in Table 25-5 in a production system because all non-historic events are stored in partition_last and partition_last_pin. You need to add real-time and delayed partitions.

Table 25-6 Real-Time Partitioning Enabled, Real-Time Partitions Exist, Delayed Partitioning Enabled

Partition Partition Partition Partition Partition Partition Partition Partition

partition_historic

RT P_1

RT P_2

RT P_3

RT P_4 through RT P_(N-1)

RT P_N

partition_last_pin

partition_last


The event table is partitioned as shown in Table 25-6 if you enable partitions, create real-time partitions, and enable delayed-event partitions.

Caution:

Do not use the partition scheme shown in Table 25-6 in a production system because all non-historic delayed events are stored in partition_last. You need to add delayed partitions.

Table 25-7 Real-Time Partitioning Enabled, No Real-Time Partitions, Delayed Partitioning Enabled, Delayed Partitions Exist

Partition Partition Partition Partition Partition Partition Partition Partition

partition_historic

partition_last_pin

D P_1

D P_2

D P_3

D P3 through D P_(N-1)

D P_N

partition_last


The event table is partitioned as shown in Table 25-7 if you enable partitions, do not create real-time partitions, and enable and create delayed partitions.

Caution:

Do not use the partition scheme shown in Table 25-7 in a production system because all non-historic real-time events are stored in partition_last_pin. You need to add real-time partitions.

Table 25-8 Real-Time Partitioning Enabled, Real-Time Partitions Exist, Delayed Partitioning Enabled, Delayed Partitions Exist

Partition Part'n Part'n Part'n Part'n Part'n Part'n Part'n Part'n Part'n Part'n

partition_historic

RT P_1

RT P_2

RT P_3 through RT P_(N-1)

RT P_N

partition_last_pin

D P_1

D P_2

D P_3 through D P_(N-1)

D P_N

partition_last


The event table is partitioned as shown in Table 25-8 if you enable partitions, create real-time partitions, and enable and create delayed partitions.

About Managing Partitions

To manage partitions, you perform the following tasks:

Before your BRM production system begins generating objects that you wish to store in partitions, you should add partitions to your tables if any of the following situations is true:

  • You removed the 12 default monthly event partitions created during installation (event tables only).

  • You chose not to create the 12 default monthly event partitions when you enabled partitioning during installation.

  • You partitioned your tables by upgrading your database.

  • You enabled partitioning for any non-event tables.

In addition, after your partitioned production system is running, you should add purgeable partitions whenever the time periods covered by the existing purgeable partitions are about to pass.

Caution::

  • The partition_last partition is not intended to store objects; it is a temporary container for them. To keep objects out of these partitions, you need to add partitions for upcoming dates (see "Adding Partitions").

  • To purge objects stored in spillover partitions, stop all delayed-event loading and add partitions and use the -f (force) parameter. Adding partitions in this way moves the data from the spillover partitions to the added partitions, but it takes much longer than adding partitions normally.

About Purging Objects

When you purge objects, you can either:

  • Remove the partitions that contain objects.

  • Purge event objects, but maintain the event partitions.

About Removing Partitions to Purge Objects

When you purge objects by removing their partitions, you specify a start date and an end date to identify the objects to be removed. For example, you can purge objects created from January 15 to March 15.

Important:

The dates you specify for purging objects might not align with the partition dates. If this is the case, only partitions that are completely within the date range are removed.

If a partition included in the start and end date includes an object associated with an open item, the partition is not removed.

Figure 25-2 shows monthly partitions, each starting on the first of the month. If you specify to purge objects created between January 15 and April 15, only objects from February 1 through April 1 are purge because those are the only objects in the date range within complete partitions.

Figure 25-2 Purging of Complete Partitions

Description of Figure 25-2 follows
Description of "Figure 25-2 Purging of Complete Partitions"

For more information, see "Purging Objects by Removing Partitions".

Important:

Purging partitions is considered safer than removing them. For more information, see "Purging Event Objects without Removing Partitions".

Tip:

Before purging objects, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be removed. See "Running the partition_utils Utility in Test Mode".

About Purging Events While Maintaining Partitions

You can delete old events without deleting partitions by specifying the last event date you want to keep. If any old events are associated with active items, those particular events are not deleted. See "Purging Event Objects without Removing Partitions".

Tip:

Before purging objects, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be removed. See "Running the partition_utils Utility in Test Mode".

Important:

Purging partitions is considered safer than removing them.

About Running the partition_utils Utility

Most partition management tasks are accomplished by using the partition_utils utility.

Caution:

After you use your most current version of the partition_utils utility, do not use the previous versions. Doing so can corrupt your database. (However, partitions created by using the previous version are supported.)

You can run only one instance of this utility at a time. If you try to run more than one instance, the utility does not run and returns an error.

Caution:

After you start the utility, do not interrupt it. It might take several minutes to complete, depending on the size of your database. If the utility is interrupted for any reason, use the partition_utils restart operation to continue the previous operation. See "Restarting partition_utils".

The partition_utils utility creates the following tables. Do not delete them or use the table names for any other purpose:

  • PIN_TEMP_DD_INFO_T

  • PIN_TEMP_TBL_NAMES_T

  • PIN_TEMP_OUT_OF_SYNC_T

  • PIN_TEMP_TAB_PARTITIONS_T

  • PIN_TEMP_HIGH_VAL_T

  • PIN_TEMP_DETAILS_T

  • PIN_TEMP_PURGE_STATISTICS_T

  • PIN_TEMP_PURGE_PARTITIONS_T

  • PIN_TEMP_PURGE_LOGIC_T

  • PIN_TEMP_PURGE_POIDS_T

Running the partition_utils Utility in Test Mode

When you add, remove, or enable partitions, you can use the -p parameter to write an SQL statement of the operation to the partition_utils.log file without performing any action on the database. The partition_utils.log file is in the same directory as the partition_utils utility.

Caution:

Do not copy the SQL statements and run them against the database. This action is not supported.

The naming convention for real-time partitions is:

P_R_MMDDYYYY

For example, the following partition name specifies that the last date used for objects is June 29, 2004.

P_R_06292004

The naming convention for delayed-event partitions is:

P_D_MMDDYYYY

For example, the following partition name specifies that the last date used for objects is June 29, 2004.

P_D_06292004

Configuring a Database Connection

Before you use the partition_utils utility, configure the database connection parameters in the partition_utils.values file in BRM_Home/apps/partition_utils. For example:

$MAIN_DB{'vendor'}   =  "oracle";
$MAIN_DB{'alias'}    =  "pindb.myserver.com";
$MAIN_DB{'user'}     =  "pin";
$MAIN_DB{'password'} =  "pin";
 

For more information, see the comments in the partition_utils file.

Improving Performance When Using partition_utils

For all partition_utils operations, you can run processes in parallel to improve performance. Edit the NUM_OF_PROCESSES parameter in the partition_utils.values file in BRM_Home/apps/partition_utils to run processes in parallel. This parameter controls how many partition_utils processes may run in parallel, except when executing the enable operation.

The valid range for NUM_OF_PROCESSES is from 1 to 10. The default is 2.

Restarting partition_utils

If partition_utils is interrupted for any reason, it is best to use partition_utils with the restart operation to continue the previous operation. This will prevent your database partitions from becoming unaligned and corrupted.

The syntax is:

partition_utils -o restart [-b]

Use the -b (bypass) parameter to bypass the last operation and clean the status of it.

For more information, see "partition_utils".

Adding Partitions

To add partitions, run the partition_utils utility with the add operation.

Important:

The syntax is:

partition_utils -o add -t realtime|delayed -s start_date 
                   -u month|week|day -q quantity [-c storable_class] [-w width] 
                  [-f] [-p]
 
  • Use the -t (type) parameter to add real-time or delayed partitions.

    Note:

    Non-event partitions are always real-time. You may only add delayed-event object partitions. To do so, you must enable partitioning for each event type you want to partition. See "Enabling Delayed-Event Partitions".
  • Use the -s (start date) parameter to specify the start date for the first of the new partitions. The format is MMDDYYYY.

    The start date must be the day after tomorrow or later; you cannot create partitions starting on the current day or the next day. For example, if the current date is January 1, the earliest start date for the new partition is January 3.

    Note:

    When adding partitions, an additional partition for the time up to start_date will be created by default if no partition for that time exists.

    By default, the start date must also be earlier than six months from today. You can change these defaults by editing the partition_utils.values file in BRM_Home/apps/partition_utils. See "Customizing Partition Limitations".

    If you try to create a partition with a start date within the current partition, the utility reports an error and provides the earliest date that you can use for the new partition.

    Note:

    To override this limitation, use the -f parameter.
  • Use the -u (unit) parameter to specify the time unit for the partition.

  • Use the -q (quantity) parameter to specify the number of partitions to add. Enter an integer greater than 0.

    By default, you can add the following numbers of partitions:

    • No more than 60 daily partitions

    • No more than 15 weekly partitions

    • No more than 6 monthly partitions

    You can change these defaults by editing the partition_utils.values file in BRM_Home/apps/partition_utils. See "Customizing Partition Limitations".

  • Use the -c (storable class) parameter to specify the class of objects to be stored in the partition.

    Note:

    If you want to specify a non-event class, partitioning must have been enabled for that class before pin_setup was run during the BRM installation process. For more information, see "Editing the pin_setup.values File to Enable Partitioning for Non-Event Tables" in BRM Installation Guide.
  • Use the -w (width) parameter to specify the number of units in a partition; for example, a partition that is 3 days wide, 2 weeks wide, or 1 month wide. Enter an integer greater than 0. The default is 1.

    By default, the maximum widths you can use are:

    • 5 days

    • 3 weeks

    • 2 months

    You can change these defaults by editing the partition_utils.values file in BRM_Home/apps/partition_utils. See "Customizing Partition Limitations".

    The default storable base class for partition_utils is the /event base class.

  • Use the -f (force) parameter to create a partition with a start date that falls within the time period of an active partition. Before you use this parameter:

    • For real-time partitions, stop all BRM server processes before adding partitions.

    • For delayed-event partitions, stop all delayed-event loading before adding partitions.

      Important:

      If you use the -f parameter to create partitions within the time period of the current partition, and you do not stop BRM processes or loading, the BRM server might write data to a partition that is in the process of being divided. This can result in a server error, such as a write operation error.

      Note:

      The -f parameter works differently when you remove partitions. In that case, it forces the removal of objects associated with open items.
  • Use the -p (print) parameter to run the utility in test mode and generate an SQL statement. See "Running the partition_utils Utility in Test Mode".

For more information, see "partition_utils".

The following examples show how to add partitions.

Note:

Adding partitions will add the requested number of partitions along with an additional partition on the given start date unless a partition labeled with that date exists already.

The following command adds five real-time event partitions, starting on July 27, 2004, and one partition for event up July 27, 2004. Each partition includes events for a two-week period:

partition_utils -o add -t realtime -s 07272004 -u week -w 2 -q 5 

The following command adds the -f parameter. This creates partitions even if they include dates in the current partition:

partition_utils -o add -t realtime -s 07272004 -u week -w 2 -q 5 -f

The following command adds six daily event partitions starting on July 27, 2004, and one partition for events up to July 27, 2004. Because the -w parameter is not used, each parameter is one day long:

partition_utils -o add -t delayed -s 07272004 -u day -q 6

The following command adds 12 monthly real-time partitions for /journal class starting on July 27, 2004, and one partition for /journal up to July 27, 2004:

partition_utils -c /journal -o add -t realtime -s 07272004 -u month -q 12

The following command creates five weekly partitions for /journal class starting on July 27, 2004, and one partition for /journal up to July 27, 2004:

partition_utils -c /journal -o add -t realtime -s 07272004 -u week -q 5

Enabling Delayed-Event Partitions

To add delayed-event partitions, run the partition_utils utility with the enable operation. You must enable delayed-event partitioning before you can add delayed-event partitions.

Important:

After you enable delayed-event partitions, add partitions for delayed events before you use the system for production. Otherwise, delayed events are stored in partition_last. See "About Objects Stored in partition_last and partition_last_pin".

Note:

You can enable delayed partitions only for event objects. All non-event partitions are real-time partitions.

The syntax is:

partition_utils -o enable -t delayed [-c storable_class] [-p]
  • Use the -c (storable class) parameter to specify the event class to partition. Because delayed-event partitions cannot be used for other classes, this specific version of -c does not specify a different base class.

  • Use the -p (print) parameter to run the utility in test mode and generate an SQL statement. See "Running the partition_utils Utility in Test Mode".

    Note:

    When you enable delayed partitions, the partition_utils utility automatically runs the update operation to synchronize partitioning schemes across all tables. See "Updating Partitions".

For more information, see "partition_utils".

The following command enables delayed-event partitioning for the /event/delayed/session/telco/gsm class:

partition_utils -o enable -t delayed -c /event/delayed/session/telco/gsm

You can enable partitioning for all subclasses of an event by using the percent sign (%) as a wildcard:

partition_utils -o enable -t delayed -c /event/session/%

Note:

This operation supports only delayed events. If you specify anything other than -t delayed, the utility returns an error.

Disabling Delayed-Event Partitioning

If you enable delayed-event partitions for an event class, but then decide you do not need partitions for that delayed-event class, you can use sqlplus to remove delayed-event partitions for that event table and then remove the partition_last partition for that event table.

Important:

If you remove partitions for a delayed-event table, remove all the delayed partitions before removing the partition_last partition.

For example, to disable partitioning for the EVENT_DLYD_SESSION_TELCO_GSM_T table, do the following:

  1. Use the following sqlplus statement to ensure that there is no data in partition_last:

    SQL> select count (*) from event_dlyd_session_telco_gsm_t partition PARTITION_LAST
    

    If there is data in partition_last, add partitions to move the data.

    Caution:

    You must use the -f parameter. See "Adding Partitions".
  2. Use the following sqlplus statement to list the delayed partitions in the table:

    SQL> select partition_name from user_tab_partitions where table_name = UPPER(event_dlyd_session_telco_gsm_t) and partition_name like 'P_D_%';
    
  3. Use the following sqlplus statement to remove the listed partitions individually:

    SQL> alter table event_dlyd_session_telco_gsm_t drop partition partition_name
    
  4. Use the following sqlplus statement to remove partition_last:

    SQL> alter table event_dlyd_session_telco_gsm_t drop partition PARTITION_LAST
    
  5. Use the following sqlplus statement to verify that there are no partitions in the table:

    SQL> select count(*) from user_tab_partitions where table_name = UPPER(event_dlyd_session_telco_gsm_t) and partition_name like 'P_D_%';
    

    This should return zero rows.

Updating Partitions

The update operation allows you to align table partitioning schemes for all subclass tables with the current partitioning scheme for their base class table. Updating partitions enforces the following rules:

  • From the day after tomorrow, all tables with real-time objects will have the same real-time partitioning scheme as their base table (EVENT_T for /event base class tables, ITEM_T for /item base class tables, etc.).

  • From the day after tomorrow, all tables with delayed events will have the same delayed-event partitioning scheme as the EVENT_T table.

You need to update partitions in the following cases:

  • When you add custom partitioned classes.

  • When you extend an existing class with an array or substruct that adds a partitioned table.

  • When you add a component that includes new partitioned classes.

    Note:

    The update operation automatically runs once when you enable delayed partitions.

To align the partition schemes of new or changed object tables with the partition schemes of their base classes, run the partition_utils utility with the update operation.

The syntax is:

partition_utils -o update [-c storable_class] [-p]

For more information, see "partition_utils".

Purging Objects by Removing Partitions

The general way to purge partitioned data is to remove one or more partitions. This frees up database space. By default, partitions that contain objects associated with open items are not removed when you remove partitions.

Caution:

You can use the -f (force) parameter to remove partitions even if the objects are associated with open items, but this parameter must be used with care.

Important:

Before removing partitions that contain objects associated with open items, verify that doing so does not contradict your business practices.

Tip:

Before removing partitions, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be removed. See "Running the partition_utils Utility in Test Mode".

To remove partitions, run the partitions_utils utility with the remove operation. The syntax is:

partition_utils -o remove -s start_date -e end_date [-c storable_class][-t realtime|delayed][-f][-p]
  • Use the -s (start date) parameter to specify the start of the date range for the partitions you want to remove. The format is MMDDYYYY.

  • Use the -e (end date) parameter to specify the end of the date range for the objects you want to remove. The format is MMDDYYYY.

    All partitions that are entirely within these dates are removed. See "About Purging Objects".

    By default, you can use this operation to remove only those partitions that are older than 45 days. You can change this limitation by editing the partition_utils.values file in BRM_Home/apps/partition_utils. See "Customizing Partition Limitations".

  • Use the -c (storable base class) parameter to specify the objects you are purging by base class. The default is /event.

    Important:

    To remove a non-event table partition, you must use the -f parameter. Operations using this parameter cannot be undone and will remove objects that are being used. Use with caution.
  • Use the -t (type) parameter to remove only real-time events or delayed events for delayed-event partitions. The default is to remove both real-time and delayed objects.

  • Use the -f parameter to remove partitions even if they contain objects associated with open items. By default, partitions that contain events associated with open items are not removed.

    Note:

    The -f parameter works differently when you add partitions. In that case, it forces the splitting of partitions even when they fall within the date range of the current partition.
  • Use the -p (print) parameter to run the utility in test mode and generate an SQL statement. See "Running the partition_utils Utility in Test Mode".

For more information, see "partition_utils".

The following command removes the partitions with real-time events from July 20, 2004, to September 20, 2004:

partition_utils -o remove -s 07202004 -e 09202004 -t realtime

The following command removes real-time partitions for item table entries from July 20, 2004, to September 20, 2004:

partition_utils -o remove -s 07202004 -e 09202004 -c /item -t realtime -f

Purging Event Objects without Removing Partitions

You may purge event partitions without removing the partitions.

Tip:

Before purging events, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be purged. See "Running the partition_utils Utility in Test Mode".

To purge events without removing partitions, run the partitions_utils utility with the purge operation. The syntax is:

partition_utils -o purge -e end_date [-t realtime|delayed] [-p]
  • Use the -e (end date) parameter to specify the last date for the events you want to keep. The format is MMDDYYYY. All events older than that date that are associated with closed items are deleted. See "About Purging Objects".

  • Use the -t (type) parameter to purge only real-time or delayed events. The default is to purge both real-time and delayed events.

  • Use the -p (print) parameter to run the utility in test mode and generate an SQL statement. See "Running the partition_utils Utility in Test Mode".

For more information, see "partition_utils".

The following command purges real-time events before July 20, 2004:

partition_utils -o purge -e 07202004 -t realtime

Finding the Maximum POID for a Date

You can use partition_utils -o maxpoid to find the maximum POID in a partition for a given date. You might need this if you have scripts to automatically manage partitions.

The syntax is:

partition_utils -o maxpoid -s date -t realtime|delayed

The following command finds the maximum POID for a real-time partition:

partition_utils -o maxpoid -s 02012005 -t realtime

For more information, see "partition_utils".

Customizing Partition Limitations

The partition_utils utility specifies default limitations to prevent the creation of an unrealistic number of partitions. You can change these limitations by editing the partition_utils.values file in BRM_Home/apps/partition_utils.

The default limitation values are:

  • Partition start date: later than the day after tomorrow, earlier than 6 months from now.

  • Maximum number of partitions:

    • 60 daily

    • 15 weekly

    • 6 monthly

  • Maximum widths:

    • 5 days

    • 3 weeks

    • 2 months

  • Removed partitions must be at least 45 days old.

  • Minimum required percent of purgeable data.

  • Partitions will be purged only when purgeable POIDs are greater than 70% or the total purgeable POIDs in the EVENT_BAL_IMPACT_T table.

    The valid range for this setting is from 60 to 100.

  • Records are deleted only if there is a specified number in a chunk. The default is 1000 records.

    The valid range is from 500 to 5000.

  • When purging, the DELETE_IN_PLACE method is used if the number of purgeable events is greater than 5% of the total events or if there are more than 10,000 purgeable records.

    You can change the number of purgeable records required. The valid range is from 1000 to 20000.

Customizing the List of Events Stored in partition_historic

If you do not need to save all the event types stored by default in partition_historic or if you need to save additional classes of events, use the Storable Class Editor in Developer Center to customize the list of nonpurgeable event classes.

Use the Non-purgeable class property to specify if an event is purgeable.

Caution:

To avoid storing any event objects you do not want to purge in purgeable partitions, you must customize event classes stored in partition_historic before running BRM and generating any events.

For more information, see "About Purging Data".

Note:

The Non-purgeable property for /event classes has no effect unless your event tables are partitioned.