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 Mirror 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 standby 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 Mirror Replication Agent configuration properties, see the Mirror 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 ASE standby, 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 standby 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 standby database.
