The pitfalls of using pseudo column SYSDATE in "where" clause in compare or repair.
The compare and repair commands have an extremely useful option named “where”. If used in the right manner, it can be used to correct out of sync for rows that satisfy a condition, thereby reducing the number of rows that need to be compared to bring the target table in sync. This can speed up the compare and repair and even the largest of the tables can be sync’d in a short time. It is also used frequently in conjunction with a DATE type of field to determine the out of sync rows that occurred after a particular date. However, if the SYSDATE pseudo column is used in the “where” clause, this will give incorrect results and instead of detecting or fixing the out of sync, the compare will end up inducing out of sync. Here is a short illustration.
The user wants to ascertain if a huge table is in sync for all the changes that occurred in the past 24 hours and fix the out of sync in the process. Assume that the tables are in sync and we run a repair not knowing that the tables are in sync. The source and target tables have a field named UPDATE_DATE that stores the value of the date on which the row was first inserted or subsequently updated. To check for out of sync for rows created/updated in past 24 hours and repair them, the user runs the following repair on the source:
sp_ctrl>repair OWNER.TABLE_NAME where “UPDATE_DATE >(SYSDATE-1)”
The problem is, the repair process will first gather rows by taking a read consistent view on source using the condition and then the repair marker will travel to target and then gather the rows on target. There will be a slight time lag in the process and on target it will return a set of rows with slight difference (due to the pseudocolumn SYSDATE which can return different values across source and target since the read consistent view cannot be issued instantaneously across source and target). The repair process will then get faulty results as it is comparing apples with oranges, if you will. If you really want this to succeed, you will have to use a constant value (literal) and not an identifier (variable) like SYSDATE that can return different values across source and target. Not all variables cause problems in "where" clause and the SYSDATE is an exception due to the way it is evaluated.
This is why using SYSDATE in compare is fraught with errors.
© 2025 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center