Capturing Change Data Without Change Data Capture in Oracle

Prior to the introduction of Change Data Capture, there were a number of ways that users could capture change data, including table differencing and change-value selection.

Table differencing:
Table differencing involves transporting a copy of an entire table from the source (production) database to the staging database (where the change data is captured), where an older version of the table already exists. Using the SQL MINUS operator, you can obtain the inserted and new versions of updated rows with the following query:

SELECT * FROM new_version
MINUS SELECT * FROM old_version;

However, there are several problems with this method:
1.It requires the entire table thereby increasing the cost of transportation.
2.Cost of MINUS operation is high.
3.Any intermediate price values between the old and new versions of the product's table cannot be captured using table differencing.
4.There is no way to determine which changes were made as part of the same transaction.

Change-value selection:
Change-value selection involves capturing the data on the source database by selecting the new and changed data from the source tables based on the value of a specific column.
However, there are also several problems with this method:
1.The overhead of capturing the change data must be borne on the source database.
2.This method is no better at capturing intermediate values than the table differencing method.
3.This method is also no better than the table differencing method at capturing which data changes were made together in the same transaction.
4.You have to design your source database in advance with this capture mechanism in mind – all tables from which you wish to capture change data must have a change-value column.