How to force Shareplex to ignore a transaction. (16835)

There are times when you want to ignore or disable INSERT, UPDATE, DELETE or DDL on a table, or ignore a transaction involving one or many tables, Can Shareplex be configured to ignore a transaction?

There are different ways of doing this and not all of the following may work in all situations:

1. create_ignore.sql - The easiest way is to run this script prior to running your transaction. It will ignore all your transactions during the time of your session up to the first commit or rollback. The script is located in /proddir/util subdirectory. You may note that this will suit only the batch type of transactions due to the need to execute this script every time prior to running the transaction to be ignored.

2. Transformation - a feature within Shareplex that can be used to direct the post process to call a PL/SQL procedure instead of applying a SQL operation to the target database. Transformation supports only INSERT, UPDATE and DELETE operations and enables replicated data to be manipulated before, or instead of, posting.

3. If you are on 4.5 or higher, use the "SP_OPO_DISABLE_OBJECT_NUM" parameter. If this parameter is enabled, it will direct Post to ignore all transactions (including DDL) for a particular table. To set this parameter:

Obtain the Oracle object_id of the object from the SOURCE system, then on the target issue the following:

sp_ctrl> set param sp_opo_disable_object_num <object_id from source database>
sp_ctrl> stop post
sp_ctrl> start post


To set this back to normal;

sp_ctrl> reset param sp_opo_disable_object_num
sp_ctrl> stop post
sp_ctrl> start post

With 1 and 2 above, the advantage may be that Shareplex will not Capture any change so the queues will not build up. However, there may be overheads involved when a PL/SQL routine is invoked during the step 2. Moreover, 1 and 3 are easier to administer or configure whereas 2 is more involved due to the efforts required to create PL/SQL routines.