SharePlex 8.6.6 - Installation Guide

Control Oracle DDL replication

By default, SharePlex replicates some Oracle DDL for objects that are listed in the active configuration (explicitly or by wildcard), but you can expand this support with parameter settings.

Important!

• For the most current information about supported DDL and requirements or limitations, see the SharePlex Release Notes provided with this release. That information may supercede what is documented here.
• DDL replication is supported only to Oracle targets, except for ALTER TABLE to ADD COLUMN or DROP COLUMN, which are supported for all SharePlex targets.

SharePlex provides default DDL support for objects in the configuration file. You can expand this support through parameter settings.

See the SharePlex Release Notes for detailed information about the DDL that is supported by SharePlex.

Default support for Oracle DDL

SharePlex provides some basic DDL support by default.

DDL for existing objects

By default SharePlex replicates the TRUNCATE TABLE command and the ALTER TABLE command to ADD or DROP columns when:

• the affected object exists in the source and target at the time of activation and
• its name is listed in the configuration file (explicitly or through wildcard).

This functionality is controlled by the SP_OCT_REPLICATE_DDL parameter. A setting of 1 replicates ALTER but not TRUNCATE. A setting of 2 replicates TRUNCATE but not ALTER. The default setting of 3 replicates ALTER and TRUNCATE.

DDL for objects added after activation

By default, the SharePlex Auto-Add feature is also enabled to provide DDL support for tables and indexes that are created after activation. When SharePlex detects a CREATE statement for one of these objects and its name satisfies a wildcard in the configuration file, SharePlex does the following:

• replicates the CREATE to add the object to the target
• adds the object to replication
• maintains that object through future DDL and DML changes

The Auto-Add feature is controlled by the SP_OCT_AUTOADD_ENABLE parameter, which is set to 1 (enabled) by default.

See the SharePlex Reference Guide for details about this parameter.

Optional DDL on objects in replication

You can enable the replication of the following DDL when it is issued on objects that are in the configuration file. To enable the replication of a DDL command, set the associated parameter to 1.

See the SharePlex Reference Guide for details about these parameters.

Optional Auto-Add support for Oracle DDL

You can expand Auto-Add support to include any of the object types listed in the following table. The object is added to replication if its name satisfies a wildcard specification in the active configuration file.

To enable auto-add of individual object types

1. Make certain the SP_OCT_AUTOADD_ENABLE parameter is set to 1.

2. Set the appropriate parameter to 1, using the following table as your guide.

   DDL to auto-add	Parameter to set to 1	Additional requirements
   CREATE / DROP SEQUENCE	SP_OCT_AUTOADD_SEQ	Set the SP_OCT_TARGET_COMPATIBILITY parameter to at least 8.6.3.
   CREATE /DROP MATERIALIZED VIEW*	SP_OCT_AUTOADD_MV	Set the SP_OCT_TARGET_COMPATIBILITY parameter to at least 8.6.2.

* Note: SharePlex does not replicate materialized views to materialized views. SharePlex converts a CREATE MATERIALIZED VIEW to a CREATE TABLE, applies the CREATE TABLE to the target, and then replicates the DML that populates the view. SharePlex replicates DROP MATERIALIZED VIEW, but not ALTER MATERIALIZED VIEW.

See the SharePlex Reference Guide for details about these parameters.

Expanded DDL support for objects outside replication

You can configure SharePlex to replicate DDL for certain objects that are not listed in the configuration file. SharePlex replicates the DDL statements, but does not replicate any data change operations made to the objects because they are not part of active replication. Therefore, SharePlex does not maintain synchronization of these objects on the target. The objects must exist in the source and target prior to configuration activation.

Note: Expanded DDL replication supports not only tables and sequences but also a wide range of other objects such as procedures, functions, users, and views, which are not part of replication. Some of these objects may have underlying objects that are in replication. In those cases, Expanded DDL replication applies not only to the object that is outside the replication configuration, but also to the underlying objects that are in replication.

To replicate DDL for objects outside the replication configuration

Set the SP_OCT_REPLICATE_ALL_DDL parameter to 1.

See the SharePlex Reference Guide for details about this parameter.

See the SharePlex Release Notes for more information about supported DDL for objects that are not in the configuration file.