Alter, rename, or truncate operations during replication

When Enterprise Replication is active and data replication is in progress, you can perform many types of alter, rename, or truncate operations on replicated tables and databases.

Most of the supported operations do not require any special steps when performed on replicated tables or databases; some, however, do require special steps. None of the supported alter, rename, or truncate operations are replicated. You must perform these operations on each replicate participant.

You can run the alter, rename, and truncate operations that are listed in the following table on active, replicated tables or databases without performing extra steps.
Table 1. Requirements for operations on replicated tables
Operation Requirements
Add or drop default values and SQL checks None
Add or drop fragments Requires mastered replicate to be defined
Add or drop unique, distinct, and foreign keys None
Alter the locking granularity None
Alter the next extent size None
Change an existing fragment expression on an existing dbspace Requires mastered replicate to be defined
Convert a fragmented table to a non-fragmented table Requires mastered replicate to be defined
Convert a non-fragmented table to a fragmented table Requires mastered replicate to be defined
Convert from one fragmentation strategy to another Requires mastered replicate to be defined
Create a clustered index Requires mastered replicate to be defined
Modify the data type of a replicated column Requires mastered replicate to be defined
Modify the data type of a replicated column in a multiple-column replication key Requires mastered replicate to be defined
Move a fragment expression from one dbspace to another dbspace Requires mastered replicate to be defined
Move a non-fragmented table from one dbspace to another dbspace Requires mastered replicate to be defined
Recluster an existing index Requires mastered replicate to be defined
Rename a database None
Rename a replicated column Requires non-strict mastered replicate to be defined
Rename a table Requires non-strict mastered replicate to be defined
Truncate a replicated table Requires mastered replicate to be defined
You can perform the following alter operations on active, replicated tables, but you must perform extra steps, which are described in following sections:
  • Add a column to a replicated table
  • Remove a column from replication
  • Attach a fragment to a replicated table
  • Change or recreate a replication key

Enterprise Replication uses shadow replicates to manage alter operations on replicated tables without causing any interruption to replication. By using shadow replicates, the replicate participants SELECT clause can be modified while replication is active. For example, a new column can be brought into the replicate definition, an existing replicated column can be removed from the replicate definition and the data type or size of a replicated column can be changed without interrupting replication. See Defining Shadow Replicates for more information about shadow replicates.

Before altering a replicated table, ensure that you have sufficient log space allocated for long transactions, a sufficient number of locks available, and sufficient space available for the queue sbspace.

When you issue a command to alter a replicated table, Enterprise Replication places the table in alter mode before performing the alter operation. Alter mode is a state in which only DDL (data-definition language) and SELECT operations are allowed but DML (data-manipulation language) operations are not allowed. After the transaction that initiated the alter operation completes, Enterprise Replication unsets alter mode. Any schema changes are automatically applied to any delete tables.

The following restrictions apply when you use alter operations on replicated tables.
  • Enterprise Replication must be in an active state, unless you are only adding or dropping check constraints and default values.
  • Tables must have a master replicate defined.
  • The DROP TABLE statement is not supported.
Recommendation: If you need to perform more than one alter operation, enclose them in a single transaction so that alter mode only needs to be set and unset one time.

For a list of common alter operation problems and how to solve them, see Troubleshooting Tips for Alter Operations.