Replication of the truncate partition Command

Replication Agent supports replication of the truncate partition command.

Replicate the truncate partition command either by:

Using lr_send_trunc_partition_ddl
Wrapping truncate partition in a stored procedure and replicating the procedure

Use lr_send_trunc_partition_ddl

Use the Replication Agent configuration parameter lr_send_trunc_partition_ddl to determine whether truncate partition commands are sent as DDL or DML to the replicate database. The configuration can be:

true (default) – the truncate partition command is sent as a DDL command (alter table). Use this setting to replicate to Oracle.
false – the truncate partition is sent as a DML operation. Use this setting when replicating to databases that treat truncate partition commands as DML (for example, Microsoft SQL Server).

For information about Replication Agent configuration properties, see the Replication Agent Reference Manual.

Wrap the truncate partition command

You can wrap the truncate partition command in a stored procedure definition and replicate the procedure.

For example, to replicate truncate partition commands from an Oracle primary to an Adaptive Server Enterprise replicate, create this stored procedure at the primary database:

create procedure sp_truncate_partition
as
begin
execute immediate ‘ALTER TABLE myTable TRUNCATE PARTITION part1’;
end;

Create a corresponding stored procedure at the replicate database:

create proc sp_truncate_partition as
truncate table myTable part1

Mark the sp_truncate_partition procedure for replication. When sp_truncate_partition is executed at the primary database, the truncate partition command is replicated to the replicate database.