Oracle publication triggers send data for non-published columns when replicating with Microsoft SQL Server

Article
06/20/2023

This article helps you resolve the problem that occurs when use transactional replication with an Oracle Publisher and column filtering.

Original product version: SQL Server
Original KB number: 2310152

Symptoms

When you use transactional replication with an Oracle Publisher and column filtering, you notice that the trigger generated by the publication wizard (row level trigger) is keeping track of all columns of the table even though only a few are published.

Cause

When a vertical filter is applied to a publication that is created using an Oracle publisher configured with the Gateway option, SQL Server restricts the tracking table to only include the columns of interest but it does not restrict the triggering events. As a result, when there are many updates to table columns that are not included in the tracking table, many unnecessary rows are logged to the tracking table.

Workaround

You can work around the problem by manually altering the database trigger that is generated on the Oracle database during replication setup, to include an explicit column list to restrict the columns that on update will cause the trigger to fire.

Use the following procedure to implement the workaround:

Verify that the Oracle publisher is configured as a Gateway publisher
Mark either the published table or its associated tablespace as read-only.
Determine the name of the row trigger and log table associated with the published table.
Modify the clause of the row trigger that identifies the firing conditions to include an explicit column list containing only the published columns.
Restore the published table or its associated tablespace to read write.

For more information about each step, see the following paragraphs:

How to mark either the published table or its associated tablespace as read-only:

Before you modify the trigger, you will want to set the table to read-only so that no changes are lost. If your Oracle version predates 11 g, you will need to mark the tablespace associated with the table as read-only. Making a tablespace read-only prevents write operations on the datafiles in the tablespace. To determine which tablespace is associated with your published table, run the following query:

select table_name, tablespace_name from all_tables

where table_name = 'my_table' and owner = 'my_name';

To make a datafile read-only use a command similar to the following:

ALTER TABLESPACE my_tablespace READ ONLY;

In Oracle 11 g, you can mark a table as read-only directly.

ALTER TABLE my_table READ ONLY;

How to mark either the published table or its associated tablespace as read-only:

select table_name, tablespace_name from all_tables

where table_name = 'my_table' and owner = 'my_name';

To make a datafile read-only use a command similar to the following:

ALTER TABLESPACE my_tablespace READ ONLY;

In Oracle 11 g, you can mark a table as read-only directly.

ALTER TABLE my_table READ ONLY;

How to determine the name of the row trigger and log table associated with the published table:

All of the log tables and triggers have names constructed from the following templates:

HREPL_ARTICLE N LOG_ V
HREPL_ARTICLE N_TRIGGER_ROW

where N and V are determined in the following manner:

N is the article_id associated with the published table, which can be obtained using the following SQL query at the distributor:

select name, table_id from distribution.dbo.IHarticles

V is a version designator. If two-log tables have the same associated value for N, the active log has the larger V value.

How to modify the clause of the row trigger that identifies the firing conditions to include an explicit column list containing only the published columns:

Oracle provides a free GUI tool called SQL Developer that is well suited to making the needed modifications to the generated trigger. You can use the following steps in the SQL Developer tool to make the necessary modifications:

Open a connection to your Oracle instance.
Under the defined Oracle schemas expand the schema corresponding to your replication administrator user.
Locate Triggers in the list of objects and expand that to see the list of triggers owned by the replication administrator user.
Locate the trigger to be modified and highlight it. The text for the trigger will appear in the right pane.
Right click on the trigger name in the list and select Edit
The change to the trigger definition is made near to the beginning of the trigger, which will look similar to the following:
- ..create or replace TRIGGER AFTER DELETE OR INSERT OR UPDATE OF
- my_name.my_table
- FOR EACH ROW...
To modify the trigger, include after the UPDATE keyword the list of columns from the published table that were included in the article. If you expand Tables under the repl administrator schema and then expand the article log table associated with the published table, the columns to include are those appearing in the log after the five initial meta data columns that are prefaced with HREPL_. The word ON should appear at the beginning of the list of columns. The modifications needed for table my_name.my_table with published columns PK, c1 and c2 are shown below.
```
create or replace TRIGGER AFTER DELETE OR INSERT OR UPDATE ON "PK", "c1", "c2" OF my_name.my_table FOR EACH ROW...
```
When you have completed the modification, right-click on the trigger name and select compile to compile your revised trigger. Make sure that the resulting trigger is identified as being valid after compilation.

How to restore the published table or its associated tablespace to read write:

After completing the modification to the trigger, make sure to restore the tablespace or table to READ WRITE, depending on which was marked READ ONLY. The following statement makes the my_tablespace tablespace writable:

ALTER TABLESPACE *my_tablespace* READ WRITE;

In Oracle 11 g, use the alter table command to restore the table to allow modifications:

ALTER TABLE *my_table*  READ WRITE;

Use the following query in Oracle 11 g to verify that the published table is no longer read-only:

select table_name, read_only

from dba_tables

where table_name = 'my_table' and owner = 'my_owner'

Third-party disclaimer

Third-party information disclaimer

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.