There are two ways to replicate the truncate partition command:
Use the lr_send_trunc_partition_ddl configuration property
Wrap the truncate partition command in a stored procedure, and replicate the procedure
A configuration property has been added to Replication Agent, lr_send_trunc_partition_ddl, which can be used 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.
Alternately, 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 the following 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.
