pdb_setrepddl

(Oracle and Microsoft SQL Server only) Returns DDL replication status and enables or disables replication for DDL statements.

Note: 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, { all | user } ] 
     | stmt, { all | ddl_statement | ddl_statement_keyword } 
     | owner, { all | ownername } 
    } ] 
   [
        { enable[, override] | disable[, override] | default }
      | { enable, { all | marked | unmarked } }
    ]

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
```
To disable the replication of DDL statements and override any existing filtering rules, follow the pdb_setrepddl command with the disable 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 } ]

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.
For multiple Replication Agents and Replication Server Multi-Path Replication™ (Oracle only):
- all enables the replication of all DDL for the invoking Replication Agent instance within the Replication Agent group. For example:
```
pdb_setrepddl enable, all
```
- marked enables the replication of DDL only for objects that have been marked by the invoking Replication Agent instance within the Replication Agent group.
```
pdb_setrepddl enable, marked
```
- unmarked enables the replication of DDL for objects that have not been marked by any Replication Agent instance within the Replication Agent group.
```
pdb_setrepddl enable, unmarked
```
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:
1. 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.
2. Owner: Any DDL filtering rules for an object owner will be observed, but not rules involving statements affecting the object or the user.
3. Statement: Any DDL filtering rules for DDL statements will be observed, but not rules involving the user.
4. User: Any DDL filtering rules involving the user will be observed.
enable (for multiple Replication Agents) – 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.

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 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.
When using the all, marked, and unmarked keywords for DDL replication and multiple Replication Agents:
- You can also enable the replication of all DDL by omitting the all keyword:
```
pdb_setrepddl enable
```
- The marked and unmarked keywords do not override each other. For example, to enable the replication of both DDL for objects that have not been marked by any Replication Agent instance within the Replication Agent group and DDL for objects that have been marked by the invoking Replication Agent instance:
```
pdb_setrepddl enable, unmarked
pdb_setrepddl enable, marked
```
  However, marked and unmarked will override previous use of the all keyword, and the all keyword will override previous use of the marked and unmarked keywords.

Oracle Privileges for DDL Replication:

Note: Issuing GRANT ALL PRIVILEGES TO DDLUSER turns the DDL user into a superuser, like the SYS or SYSTEM user.

Revoke user permission from the DDL user to execute:

GRANT ALTER DATABASE
GRANT ALTER ROLLBACK SEGMENT
GRANT ALTER SYSTEM
GRANT ALTER TABLESPACE
GRANT ANALYZE ANY
GRANT AUDIT ANY
GRANT AUDIT SYSTEM
GRANT CREATE DATABASE LINK
GRANT CREATE ROLLBACK SEGMENT
GRANT CREATE TABLESPACE
GRANT DROP PUBLIC DATABASE LINK
GRANT DROP ROLLBACK SEGMENT
GRANT DROP TABLESPACE
GRANT LOCK ANY TABLE