pdb_setrepddl

Description

Returns DDL replication status and enables or disables replication for DDL statements.

This command is available only for Oracle and Microsoft SQL Server.

Syntax

For Replication Agent for Oracle:

pdb_setrepddl

        [   [[tablename] | [procname] | [sequence_name] | [objects, all]] |            [user[[, user] | [, all]]] |

           [stmt[[, ddl_statement] | [, ddl_statement_keyword] | [, all]]] |

           [owner[[, ownername] | [, all]]]   ]

        [, ]

        [[, enable[, override]] | [, disable] | [, default]]

For Replication Agent for Microsoft SQL Server:

pdb_setrepddl {enable|disable}

Parameters

override (Oracle only): To enable the replication of DDL statements and override any existing filtering rules, follow the pdb_setrepddl command with the enable and override keywords:

pdb_setrepddl enable, override

tablename (Oracle only): The name of a user table in the primary database. To enable or disable the replication of DDL involving a table, use the tablename parameter:

pdb_setrepddl tablename [[, enable[, override]] | [, disable] | [default]]

To list the current filter setting for a table, enter the tablename parameter alone:

pdb_setrepddl tablename

procname (Oracle only): The name of a procedure in the primary database. To enable or disable the replication of DDL involving a procedure name, use the procname parameter:

pdb_setrepddl procname [[, enable[, override]] | [, disable] | [default]]

To list the current filter setting for a procedure, enter the procname parameter alone:

pdb_setrepddl procname

sequence_name (Oracle only): The name of a user sequence in the primary database. To enable or disable the replication of DDL involving a sequence, use the sequence_name parameter:

pdb_setrepddl sequence_name [[, enable[, override]] | [, disable] | [default]]

To list the current filter setting for a sequence, enter the sequence_name parameter alone:

pdb_setrepddl sequence_name

objects, all (Oracle only): The objects keyword must be used with the keyword all and allows you to enable or disable the replication of DDL statements for all objects:

pdb_setrepddl objects, all [[, enable[, override]] | [, disable] | [default]]

To list all objects for which DDL statements are filtered, follow the pdb_setrepddl command with the objects, all keywords:

pdb_setrepddl objects, all

user (Oracle only): The user keyword allows you to enable or disable the replication of DDL statements executed by primary database users. To enable or disable the replication of DDL from a specified user, use the user parameter.

pdb_setrepddl user, user [[, enable[, override]] | [, disable] | [default]]

To list the current filter setting for a user, follow the user keyword with the user parameter:

pdb_setrepddl user, user

To list database users whose DDL statements will be filtered from replication, enter the user keyword alone:

pdb_setrepddl user

To enable or disable the replication of DDL statements for all users, follow the user keyword with the all keyword:

pdb_setrepddl user, all [[, enable[, override]] | [, disable] | [default]]

stmt (Oracle only)

The stmt keyword allows you to enable or disable the replication of DDL statements. To enable or disable the replication of DDL for a particular statement, use the ddl_statement parameter, which contains a string in the form of a DDL statement enclosed in single or double quotes:

pdb_setrepddl stmt, ddl_statement [[, enable[, override]] | [, disable] | [default]]

To list the current filter setting for a particular DDL statement, follow the stmt keyword with the ddl_statement parameter:

pdb_setrepddl stmt, ddl_statement

To enable or disable the replication of DDL for a entire set of statements, use the ddl_statement_keyword parameter, which contains a string in the form of a DDL statement keyword:

pdb_setrepddl stmt, ddl_statement_keyword [[, enable[, override]] | [, disable] | [default]]

Table 1-3 shows which DDL statements are filtered together by a DDL statement keyword.

**Table 1-3: The ddl_statement_keyword parameter**
Keyword value	DDL statements filtered
cluster	alter cluster, create cluster, drop cluster, truncate cluster
context	alter context, drop context
dimension	alter dimension, create dimension, drop dimension
directory	alter directory, drop directory
function	alter function, create function, drop function
index	alter index, create index, drop index
indextype	alter indextype, create indextype, drop indextype
java	alter java, create java, drop java
library	alter library, drop library
materialized_view	alter materialized view, create materialized view, drop materialized view
operator	alter operator, create operator, drop operator
outline	alter outline, create outline, drop outline
package	alter package, create package, drop package
package body	alter package body, create package body, drop package body
point	alter restore point, drop restore point
procedure	alter procedure, create procedure, drop procedure
profile	alter profile, create profile, drop profile
role	alter role, create role, drop role
sequence	alter sequence, create sequence, drop sequence
synonym	alter (public) synonym, drop (public) synonym
table	alter table, create table, drop table
trigger	alter trigger, create trigger, drop trigger
type	alter type, create type, drop type
type body	alter type body, create type body, drop type body
user	alter user, create user, drop user
view	alter view, create view, drop view

To enable or disable the replication of all DDL statements, follow the stmt keyword with the all keyword:

pdb_setrepddl stmt, all [[, enable[, override]] | [, disable] | [default]]

To list DDL statements that will be filtered from replication, enter the stmt keyword alone:

pdb_setrepddl stmt

owner (Oracle only): The owner keyword allows you to enable or disable the replication of DDL statements affecting an object owned by a particular user. To enable or disable the replication of DDL statements affecting objects owned by a particular user, use the ownername parameter to specify the user:

pdb_setrepddl owner, ownername [[, enable[, override]] | [, disable] | [default]]

To list the current filter setting for an object owner, follow the owner keyword with the ownername parameter:

pdb_setrepddl owner, ownername

To list the object owners for which DDL statements will be filtered from replication, enter the owner keyword alone:

pdb_setrepddl owner

To enable or disable the replication of DDL statements affecting objects for all owners, follow the owner keyword with the all keyword:

pdb_setrepddl owner, all [[, enable[, override]] | [, disable] | [default]]

enable: For Replication Agent for Oracle, the enable keyword allows you to enable the replication of DDL statements as specified by other keywords and parameters in the pdb_setrepddl command. To override any previous filtering of DDL statements, follow the enable keyword with the override keyword.

For Replication Agent for Microsoft SQL Server, the enable keyword allows you to enable the replication of DDL statements for the primary database.

disable: For Replication Agent for Oracle, the disable keyword allows you to disable the replication of DDL statements as specified by other keywords and parameters in the pdb_setrepddl command.

For Replication Agent for Microsoft SQL Server, the disable keyword allows you to disable the replication of DDL statements for the primary database. Replication of DDL statements is disabled by default in Replication Agent for Microsoft SQL Server.

To return the status of DDL replication in Replication Agent for Microsoft SQL Server, use the pdb_setrepddl command alone, without the disable or enable keywords.

default (Oracle only): If replication is not explicitly enabled or disabled for a particular owner, object, or DDL statement, the default keyword results in DDL statement filtering being enabled or disabled according to the following hierarchy:

Object: Any DDL filtering rules for an object will be observed, but not rules involving the object owner, statements affecting the object, or the user.

Owner: Any DDL filtering rules for an object owner will be observed, but not rules involving statements affecting the object or the user.

Statement: Any DDL filtering rules for DDL statements will be observed, but not rules involving the user.

User: Any DDL filtering rules involving the user will be observed.

Examples

Example 1

pdb_setrepddl stmt

(Oracle only) This command lists DDL statements that are filtered from replication.

Example 2

pdb_setrepddl stmt, 'create index'

(Oracle only) This command lists the current filter setting for the create index statement.

Example 3

pdb_setrepddl stmt, 'create index', disable

(Oracle only) This command causes Replication Agent to filter the create index statement from replication.

Example 4

pdb_setrepddl stmt, index, disable

(Oracle only) This command causes Replication Agent to filter the alter index, create index, and drop index statements from replication.

Example 5

pdb_setrepddl owner

(Oracle only) This command lists the object owners for which DDL statements will be filtered from replication.

Example 6

pdb_setrepddl owner, myuser

(Oracle only) This command lists the current filter setting for an object owner.

Example 7

pdb_setrepddl owner, myuser, disable

(Oracle only) This command causes Replication Agent to filter DDL statements affecting objects owned by the user named myuser.

Example 8

pdb_setrepddl myuser.mytable

(Oracle only) This command lists the current filter setting for the table named myuser.mytable.

Example 9

pdb_setrepddl myuser.mytable, disable

(Oracle only) This command causes Replication Agent to filter all DDL statements that affect the table named myuser.mytable.

Example 10

pdb_setrepddl owner, myuser, disable

pdb_setrepddl myuser.mytable, enable

(Oracle only) These commands cause Replication Agent to filter all DDL statements affecting objects owned by the user named myuser except the table named myuser.mytable.

Example 11

pdb_setrepddl owner, all, disable

pdb_setrepddl owner, myuser, enable

(Oracle only) These commands cause Replication Agent to replicate only DDL statements affecting objects owned by the user named myuser.

Example 12

pdb_setrepddl user, myuser, disable

(Oracle only) This command causes Replication Agent to filter DDL from the user named myuser.

Example 13

pdb_setrepddl enable, override

(Oracle only) This command enables DDL replication and overrides any existing filter settings.

Example 14

pdb_setrepddl owner, myuser, enable, override

(Oracle only) This command enables replication of DDL from the user named myuser and overrides any existing filter settings.

Example 15

pdb_setrepddl

This command returns the current DDL replication status for the primary database.

Example 16

pdb_setrepddl enable

This command enables replication of DDL commands issued into the primary database after this point in time.

Example 17

pdb_setrepddl disable

This command disables replication of DDL commands issued into the primary database after this point in time.

Usage

In addition to enabling DDL replication using pdb_setrepddl command, you must set the Replication Agent ddl_username and ddl_password parameters.
A database replication definition that enables DDL is required for DDL replication. If the use_rssd configuration parameter is set to true, the database replication definition must exist in the RSSD of the primary Replication Server before the Replication Agent resume command is invoked.
Only DDL statements that have identical syntax in the primary and replicate databases can be replicated. Replication Agent sends DDL statements using the syntax of the statements in the primary database.
If the Replication Agent has not been initialized, the pdb_setrepddl command returns an error.
The pdb_setrepddl command can only use the enable and disable options when the Replication Agent instance is in the Admin or Replication Down state.