DATE column get OOS (Out of Sync) after ALTER TABLE ... default sysdate not null (4291681)

DATE column get OOS (Out of Sync) after ALTER TABLE ... default sysdate not null

When you do a DDL "Alter table table_name add date_col default sysdate not null;" on source, it gets replicated to the target. On source, the column is created with the values of SYSDATE at that time for all rows in the table. This alter table statement is replicated to the target and it takes some time to do so. By the time when the DDL travels to the target and is applied to the target, it references the targets pseudocolumn SYSDATE to populate the values in DATE column. Now you may note that the targets value is not going to be identical to that of the source because the sources pseudocolumn SYSDATE is referenced at different time and also because they may have been set to different times, in terms of seconds or fractions thereof. As a result, source and target will be OOS.

The reason for OOS is because the source and target sysdate are different when "Alter table table_name add date_col default sysdate not null;" get execute.

WORKAROUND:

Theres 2 workaround:

1:One solution is to use "Alter table table_name add date_col default trunc(sysdate) not null;". The function TRUNC() will get rid of the hours, minutes and seconds component of date when setting the column to SYSDATE. This should ensure identical values on source and target for the column DATE (assuming youre not doing this at midnight). Once that is done, you can then change the date_col default to "sysdate" without the TRUNC(). For any new INSERT from this point onward, the SYSDATE default column will have the complete SYSDATE, i.e. MMDDYYYYHH24MISS.

2: Alternatively, if you know the new date column is out of sync and you want the next update to overwrite it and not return OOS, you can set this shareplex internal parameter SP_OCT_REUCED_KEY to 2 which will not check the new date column and just use the PK for UPDATE and DELETE in the where clause.

Note: By setting this parameter to 2, shareplex will no longer detect out of sync for all replicated tables for update.

SP_OCT_REDUCED_KEY --- the default value is 1

This parameter controls the size of the WHERE clause generated by SharePlex for UPDATE and DELETE operations. If the source table has numerous columns, the size of a WHERE clause can be large even if only a few columns are changed. In general, this is unnecessary for SharePlex replication, so this flag is defaulted to generate a smaller WHERE clause, which in turn reduces memory usage and increases performance. The following explains how the different settings for this parameter determine the components used in the WHERE clause.

if it is set to 1:
� DELETEs � only the key columns.
� UPDATEs � only the key columns plus the pre-image of changed columns where
pre-image and post-images do not match.
Example (where the PK is EmpNo and the pre-image is 3000)
SQL> UPDATE scott.employee_source
SET Salary = 32000
WHERE rownum=1 and EmpNo = 3
AND Salary = 30000

If set to 2:
� DELETEs � only the key columns.
� UPDATEs � only the key columns.
Example (where the PK is EmpNo)
SQL> UPDATE scott.employee_source
SET Salary = 32000
WHERE rownum=1 and EmpNo = 3

If set to 0:
� DELETEs � all columns in the table.
� UPDATEs � the key* plus all modified columns.
Example (where the PK is EmpNo and the pre-images are 3000 and 40)
SQL> UPDATE scott.employee_source
SET Salary = 32000, dept=40
WHERE rownum=1 and EmpNo = 3 and Salary=30000 and dept=40

* Key is defined as one of the following: (1) a primary or unique key or, (2) in the absence of an Oracle key, a simulated
key constructed by SharePlex from all columns (except LONG or LOB), or (3) a key definition constructed of
user-defined columns. For more information about how SharePlex uses keys, see the SharePlex Administrator�s
Guide.

Default: 1 (flag)
Range of valid values: 0, 1 or 2
Takes effect: immediately

If you have any questions or concerns, please call Quest support for assistance.