pdb_setrepddl

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

Syntax

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 } }
    ]

Parameters

override – 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 – 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 – 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 – 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 – 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 – 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 – 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 – 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 multiple Replication Agents and Replication Server Multi-Path Replication™:
- 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 – The disable keyword allows you to disable the replication of DDL statements as specified by other keywords and parameters in the pdb_setrepddl command.
default – 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.

Examples

Example 1 –
```
pdb_setrepddl stmt
```
This command lists DDL statements that are filtered from replication.
Example 2 –
```
pdb_setrepddl stmt, 'create index'
```
This command lists the current filter setting for the create index statement.
Example 3 –
```
pdb_setrepddl stmt, 'create index', disable
```
This command causes Replication Agent to filter the create index statement from replication.
Example 4 –
```
pdb_setrepddl stmt, index, disable
```
This command causes Replication Agent to filter the alter index, create index, and drop index statements from replication.
Example 5 –
```
pdb_setrepddl owner
```
This command lists the object owners for which DDL statements will be filtered from replication.
Example 6 –
```
pdb_setrepddl owner, myuser
```
This command lists the current filter setting for an object owner.
Example 7 –
```
pdb_setrepddl owner, myuser, disable
```
This command causes Replication Agent to filter DDL statements affecting objects owned by the user named myuser.
Example 8 –
```
pdb_setrepddl myuser.mytable
```
This command lists the current filter setting for the table named myuser.mytable.
Example 9 –
```
pdb_setrepddl myuser.mytable, disable
```
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
```
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
```
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
```
This command causes Replication Agent to filter DDL from the user named myuser.
Example 13 –
```
pdb_setrepddl enable, override
```
This command enables DDL replication and overrides any existing filter settings.
Example 14 –
```
pdb_setrepddl owner, myuser, enable, override
```
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.

For Oracle 10g and 11g, grant user permission to the DDL user to execute:

GRANT ALTER ANY INDEX
GRANT ALTER ANY INDEXTYPE
GRANT ALTER ANY PROCEDURE
GRANT ALTER ANY TABLE
GRANT ALTER ANY TRIGGER
GRANT ALTER ANY TYPE
GRANT ALTER SESSION
GRANT BECOME USER
GRANT CREATE ANY INDEX
GRANT CREATE ANY INDEXTYPE
GRANT CREATE ANY PROCEDURE
GRANT CREATE ANY SYNONYM
GRANT CREATE ANY TABLE
GRANT CREATE ANY TRIGGER
GRANT CREATE ANY TYPE
GRANT CREATE ANY VIEW
GRANT CREATE INDEXTYPE
GRANT CREATE MATERIALIZED VIEW
GRANT CREATE PROCEDURE
GRANT CREATE PUBLIC SYNONYM
GRANT CREATE SYNONYM
GRANT CREATE TABLE
GRANT CREATE TRIGGER
GRANT CREATE TYPE
GRANT CREATE VIEW
GRANT DELETE ANY TABLE
GRANT DROP ANY INDEX
GRANT DROP ANY INDEXTYPE
GRANT DROP ANY MATERIALIZED VIEW
GRANT DROP ANY PROCEDURE
GRANT DROP ANY SYNONYM
GRANT DROP ANY TABLE
GRANT DROP ANY TRIGGER
GRANT DROP ANY TYPE
GRANT DROP ANY VIEW
GRANT DROP PUBLIC SYNONYM
GRANT INSERT ANY TABLE
GRANT SELECT ANY TABLE
GRANT UPDATE ANY TABLE

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