Configuring a Partitioned Oracle IDB

In Oracle environments with large amounts of data to maintain, you can choose to create a partitioned Oracle IDB, which you can then purge efficiently by truncating entire partitions using the purgePartitions811 stored procedure. During this purge, all records in the purged partitions—both terminated and non-terminated—are truncated unconditionally.

Important

• If you need to purge only non-terminated records, use the GSYSPurge80/gsysPurge81 purge procedure.
• This partitioning and purge functionality is supported only for Oracle 11g and higher.
• Genesys strongly recommends that you do not use this purge mechanism for long-lived data types, such as multimedia. When used with long-lived data types, you might encounter situations in which some of the data for a still-active interaction is purged.

Overview

Creating Your Database

About Partitioning

Purging

Partitioned Tables

Overview

The procedure for deploying the purge-by-truncating-partitions functionality is outlined below.

1. Start with a new Oracle database. There is no migration from a non-partitioned to a partitioned IDB.
2. Run the SQL scripts to create your partitioned IDB, using the standard procedure given in Deploying IDB. However, the scripts used to create a partitioned IDB differ from those used for nonpartitioned IDBs, so be sure to see “Creating Your Database” (below) for a list of the correct scripts.

Creating Your Database

To create a partitioned Oracle IDB, follow the standard instructions for Deploying IDB, but run the following scripts rather than the scripts used for a nonpartitioned IDB. These two initialization scripts create a new partitioned IDB:

• CoreSchemaPart_ora.sql
• CoreProcedures_ora.sql

The following initialization script sets up the stored procedure used to purge the partitioned Oracle IDB:

• PurgePart_ora.sql

As noted above, there is no migration path from a nonpartitioned to a partitioned database.

About Partitioning

The number of partitions is fourteen, with each partition equivalent to one day. Data is written into the partitions in sequence, starting with Partition 1 on Day 1, Partition 2 on Day 2 and so on, circling back to Partition 1 on Day 15.

As with all purge methods, only operational tables are purged. Special tables used for internal data storage and retrieval are neither partitioned nor purged.

The tables that are available for partitioning include the gsys_partition field, which must be configured to contain the UTC value taken from the created_ts field. This parameter is set using the partition-type configuration option.

Each partitioned table also includes the virtual GSYS_SHORT_DAY column, based on value of the gsys_partition field.

Purging

You perform the purge by executing the purgePartitions811 stored procedure, which truncates all partitions except for [the number you specify in the days-to-keep parameter of the SQL statement] + [an additional safe-guard or tomorrow day].

Instructions for how to run the purgePartitions811 procedure, how to schedule it, and all other operational considerations are documented in Purging by Truncating Partitions.

Partitioned Tables

The following tables are partitioned by the CoreSchemaPart_ora.sql script:

G_AGENT_STATE_HISTORY	G_IR_HISTORY	GO_CHAINREC_HIST
G_AGENT_STATE_RC	G_IS_LINK	GO_CUSTOM_FIELDS
G_CALL	G_IS_LINK_HISTORY	GO_FIELDHIST
G_CALL_HISTORY	G_LOGIN_SESSION	GO_METRICS
G_CALL_STAT	G_PARTY	GO_RECORD
G_CALL_USERDATA	G_PARTY_HISTORY	GO_SEC_FIELDHIST
G_CALL_USERDATA_CUST	G_PARTY_STAT	GO_SECURE_FIELDS
G_CALL_USERDATA_CUST1	G_ROUTE_RESULT	GOX_CHAIN_CALL
G_CALL_USERDATA_CUST2	G_USERDATA_HISTORY	GS_AGENT_STAT
G_CUSTOM_DATA_P	G_VIRTUAL_QUEUE	GS_AGENT_STAT_WM
G_CUSTOM_DATA_S	GM_F_USERDATA	GX_SESSION_ENDPOINT
G_CUSTOM_STATES	GM_L_USERDATA	G_ROUTE_RES_VQ_HIST
G_DND_HISTORY	GO_CAMPAIGNHISTORY	G_SECURE_USERDATA_HISTORY
G_IR	GO_CHAIN	GO_CAMPPROP_HIST