How to figure out which record has been deleted in an effiecient way?

Question

I am working on an in-house ETL solution, from db1 (Oracle) to db2 (Sybase). We needs to transfer data incrementally (Change Data Capture?) into db2.

I have only read access to tables, so I can't create any table or trigger in Oracle db1.

The challenge I am facing is, how to detect record deletion in Oracle?

The solution which I can think of, is by using additional standalone/embedded db (e.g. derby, h2 etc). This db contains 2 tables, namely old_data, new_data.

old_data contains primary key field from tahle of interest in Oracle.

Every time ETL process runs, new_data table will be populated with primary key field from Oracle table. After that, I will run the following sql command to get the deleted rows:

SELECT old_data.id FROM old_data WHERE old_data.id NOT IN (SELECT new_data.id FROM new_data)

I think this will be a very expensive operation when the volume of data become very large. Do you have any better idea of doing this?

Thanks.

Answer 1

Which edition of Oracle ? If you have Enterprise Edition, look into Oracle Streams. You can grab the deletes out of the REDO log rather than the database itself