Poll Oracle DB for changes

Question

I am trying to figure out how I could poll for changes that are made to an Oracle Table without using a trigger. The only changes I currently care about are new/inserted records. Any suggestions would be greatly appreciated.

I also don't really want to have to use other tables to keep track of what has changed.

Thanks!

S

Answer 1

CDC is an option, but that may tend to be a bit heavy if you're just writing an application that needs to be notified of new data, Data Change Notification may be a more appropriate solution. That avoids the need to poll the database as well since the database can notify the application of changes.

You could also potentially use Streams to send change records to the application.

If you really want to poll the table, and assuming you are on 10g or later, you may be able to use the ORA_ROWSCN pseudocolumn. By default, that will give you the approximate SCN (system change number) of the last change to a particular block. If you don't care about getting a few spurious rows, that's probably sufficient. If you rebuild the table(s) with ROWDEPENDENCIES enabled, the ORA_ROWSCN will be tracked at the row level rather than at the table level. Of course, since ORA_ROWSCN isn't indexed, retrieving the rows that have been modified since a particular SCN would require a table scan. You would probably be better served using a sequence-generated primary key or a CREATED_DATE column in the table to track when rows were inserted.

Answer 2

You could use Oracle's Change Data Capture.

Change Data Capture efficiently identifies and captures data that has been added to, updated, or removed from Oracle Database relational tables, and makes the change data available for use by applications.

Oftentimes, data warehousing involves the extraction and transportation of relational data from one or more source databases into the data warehouse for analysis. Change Data Capture quickly identifies and processes only the data that has changed, not entire tables, and makes the change data available for further use.

Change Data Capture does not depend on intermediate flat files to stage the data outside of the relational database. It captures the change data resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is then stored in a database object called a change table, and the change data is made available to applications in a controlled way.