Replicating DDL

The advanced replication facilities include support for replicating DDL commands to all master databases in a replication group. The DBMS_REPCAT procedures that provide this support are DBMS_REPCAT.ALTER_MASTER_REPOBJECT and DBMS_REPCAT.EXECUTE_DDL; their specifications follow:

PROCEDURE alter_master_repobject(
    sname    IN VARCHAR2,
    oname    IN VARCHAR2,
    type     IN VARCHAR2,
    ddl_text IN VARCHAR2,
    comment  IN VARCHAR2 := '',
    retry    IN BOOLEAN  := FALSE);

    gname       IN VARCHAR2,
    master_list IN VARCHAR2 := NULL,
    ddl_text    IN VARCHAR2);

    gname        IN VARCHAR2,
    master_table IN dbms_utility.dblink_array,
    ddl_text     IN VARCHAR2);


Notice that ALTER_MASTER_REPOBJECT does not allow you to specify master sites, whereas EXECUTE_DDL does. ALTER_MASTER_REPOBJECT operates only on existing replicated objects and therefore executes at all master sites, whereas EXECUTE_DDL allows you to perform DDL operations independent of replicated objects. For example, you can use EXECUTE_DDL to create users at a remote site.


Note the following restrictions on DDL replication:

  • Both DBMS_REPCAT.ALTER_MASTER_REPOBJECT and DBMS_REPCAT.EXECUTE_DDL must be called from the master definition site.

  • DBMS_REPCAT.ALTER_MASTER_REPOBJECT requires the replication group to be quiesced.

  • You must call regenerate replication support for tables that you alter with a call to DBMS_REPCAT.ALTER_MASTER_REPOBJECT.


The following examples demonstrate how to use theDDL replication ...

Get Oracle Distributed Systems now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.