Executing DDL Within a Master Group

Oracle Enterprise Manager lets you propagate one or more SQL DDL statements to some or all of the master sites in a master group. This option lets you execute unique DDL that is not specifically supported within Oracle's replication management API. For example:

You might want to create supporting objects for a replicated environment. For example, you might want to coordinate the creation of sequences with the same name at each master site to support a replicated table's primary key such that uniqueness conflicts are not possible. Specifically, at each site in a shared data ownership system, you can create sequences so that each sequence generates a mutually exclusive set of sequence numbers.
You might want to perform security management related to the replicated environment. For example, you might want to grant the privileges necessary to access replicas of a table throughout a multimaster system.

Caution: Do not execute DDL that could damage global database integrity in a multimaster environment. Also, to alter the definition of a replication object in a master group, you should always use Oracle Enterprise Manager (or an equivalent replication management API call). Use of other Enterprise Manager tools or a SQL DDL command in SQL*Plus (for example, ALTER TABLE) to directly alter an object in a replicated environment does not necessarily propagate DDL changes to the object at other sites in the system.

Consider the following issues before and after executing the DDL:

When you alter a replicated table that has dependent updatable materialized views, it may be necessary to first push all changes from the materialized view site.
After altering a replicated object, you must regenerate replication support for the object.
After altering a replicated object, check the administrative requests at the master definition site to be sure that the object was successfully modified at each master site. The DDL changes to the object and any supporting objects are asynchronously applied at each master site.
After altering a replicated table that has dependent materialized views, you must drop and recreate the materialized views in order to reflect this change at the materialized view sites. All other replicated objects at a materialized view site would be automatically recreated the next time that a dependent materialized view is refreshed.

Note: Local customization of individual replicas at materialized view or master sites is outside the scope of Oracle Replication. As a replication administrator, you must ensure that local customizations do not interfere with any global customizations done with Oracle Enterprise Manager.

To execute DDL at selected master sites in a master group:

In Enterprise Manager, connect to the Database Instance Home page as the replication administrator.
Click Data Movement.
Under the Advanced Replication section, click Manage.

The Administration Overview page appears.
Under the Multimaster Replication section, click the number link next to Master Groups to open the Master Groups page.
Select the target master group that contains the object for which you want to execute the DDL and click the number in the Replicated Object column.

The Replicated Objects page appears.
Select the object for which you want to execute the DDL and click Edit.

The General subpage of the Edit Replicated Object page appears.
Click Advanced to display the Enter DDL Text field.
Enter the DDL to be executed in the Enter DDL Text field.

Note: Do not include the terminating semi-colon (;) at the end of the DDL statement.
Click Apply to execute the specified DDL at the selected master group.

API Equivalent: DBMS_REPCAT.EXECUTE_DDL

Executing DDL Within a Master Group

Related Topics