Reference for Oracle GoldenGate

Valid For

Manager, Oracle Database

Description

Use the PURGEDDLHISTORY and PURGEDDLHISTORYALT parameters to control the size of the DDL history tables that support DDL capture. These tables are created in an Oracle database to support trigger-based DDL capture.

These parameters cause Manager to purge rows that are not needed any more. You can specify the maximum and minimum amount of time to keep a row, based on the last modification date. Both maximum and minimum rules must be specified; otherwise Manager does not have a complete criteria for when to delete the row. For example, MINKEEPHOURS 3 used with MAXKEEPHOURS 5 specifies to keep rows that have not been modified in the past three hours, but to delete them when they have not been modified for at least five hours.

These parameters require a logon to be specified with the USERID or USERIDALIAS parameter.

WARNING:

Use caution when purging the history tables. They are critical to the integrity of the DDL synchronization processes. Premature purges are non-recoverable through Oracle GoldenGate. To prevent any possibility of permanent DDL data loss, make regular backups of the Oracle GoldenGate DDL schema.

Default

Purge every hour

Syntax

PURGEDDLHISTORY | PURGEDDLHISTORYALT
{, max_rule}
[, min_rule]
[, frequency]

PURGEDDLHISTORY

Purges the DDL history table. This table tracks DDL operations. To determine the name of the history table to purge, Oracle GoldenGate first looks for a name specified with the DDLTABLE parameter in the GLOBALS file. If that parameter does not exist, Oracle GoldenGate uses the default name of GGS_DDL_HIST.

PURGEDDLHISTORYALT

Purges the internal DDL history table. This table tracks partitioned object IDs that are associated with the object ID of a table. To determine the name of the internal history table to purge, Oracle GoldenGate first looks for a name specified with the DDLTABLE parameter in the GLOBALS file and appends _ALT to it. If that parameter does not exist, Oracle GoldenGate uses the default name of GGS_DDL_HIST_ALT.

max_rule

Required. Can be one of the following to set the maximum amount of time to keep rows.

MAXKEEPHOURS n: Purges if the row has not been modified for n number of hours. The minimum is 1 and the maximum is 1000.
MAXKEEPDAYS n: Purges if the row has not been modified for n number of days. The minimum is 1 and the maximum is 365.

min_rule

Can be one of the following to set the minimum amount of time to keep rows.

MINKEEPHOURS n: Keeps an unmodified row for at least the specified number of hours. The minimum is 1 and the maximum is 1000.
MINKEEPDAYS n: Keeps an unmodified row for at least the specified number of days. The minimum is 1 and the maximum is 365.

frequency

Sets the frequency with which to purge DDL history. The default interval at which Manager evaluates potential maintenance tasks is 10 minutes, as specified with the CHECKMINUTES parameter. At that interval, Manager evaluates the PURGEDDLHISTORY or PURGEDDLHISTORYALT frequency and conducts the purge at the specified frequency.

frequency can be one of the following:

FREQUENCYMINUTES n

Sets the frequency, in minutes, with which to purge DDL history. The default purge frequency is 60 minutes. The minimum is 1 and the maximum is 360.

FREQUENCYHOURS n

Sets the frequency, in hours, at which to purge DDL history.

See "CHECKMINUTES" for more information about controlling the interval between Manager maintenance checks. The minimum is 1 and the maximum is 24.

Example

The following example keeps all rows that have not been modified in the past three days and deletes them when they have not been modified for at least five days. The purge frequency is 30 minutes.

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30