A View Of Life From An Oracle Database Administrator

Monday May 06, 2013

At a client site there was a requirement for performance of a SIEBEL database to partition an existing source table that happened to be also replicated with golden gate. The requirement was only to partition the source table and NOT the replicated target table. During this process an issue occurred with duplicate data being replicated after the database partitioning occurred. This article covers the issue found in the process and a workaround to that issue using a simple test case to demonstrate. It firstly recreates the issue then secondly explains the root cause and solution.

Please note that in this example of golden gate, 'DDL' replication is NOT used. As stated in this case the partitioning is only at the source side only. It is a simple extract of the table SCOTT.EMP containing 14 rows and replicating to a new schema / table in the same database called REP.EMP. The primary key is defined on the EMP table at both sites based on the EMPNO field as is default in the SCOTT sample schema.

The document also does not cover set up for this replication as it focuses on what happens when the source table is changed into a partitioned table from a non partitioned table and replication is in place. However it does cover some basic outline steps for setting up the test-case.

Please Note these scripts were written only for demonstration purposes. They are not optimized and they have almost no error checking, so be careful!