Poll Oracle DB for changes

Question

Hi,

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

I'm not sure if this is what you're looking for but you could check max rowid and determine which records are new since last check. This should work if the rows are only inserted and not deleted.

for example: select MAX(rowid) from your_table; or select rowid from your_table where rowid > last_max;

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.

Answer 3

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 4

If you create your table with the ROWDEPENDENCIES clause you can use the ORA_ROWSCN pseudo-column to identify changed rows.

Here is a table

SQL> create table t23
  2  (name varchar2(10)
  3   , id number
  4  ) rowdependencies
  5  /

Table created.

SQL> insert into t23 values ('SAM-I-AM', 1)
  2  /

1 row created.

SQL> insert into t23 values ('KNOX', 2)
  2  /

1 row created.

SQL> insert into t23 values ('FOX', 3)
  2  /

1 row created.

SQL> insert into t23 values ('LORAX', 9)
  2  /

1 row created.

SQL> select ora_rowscn, id, rowid from t23
  2  /

ORA_ROWSCN         ID ROWID
---------- ---------- ------------------
                    1 AAB72FAAEAAA+4cAAA
                    2 AAB72FAAEAAA+4cAAB
                    3 AAB72FAAEAAA+4cAAC
                    9 AAB72FAAEAAA+4cAAD

SQL> commit
  2  /

Commit complete.

SQL> select ora_rowscn, id, rowid from t23
  2  /

ORA_ROWSCN         ID ROWID
---------- ---------- ------------------
  75288125          1 AAB72FAAEAAA+4cAAA
  75288125          2 AAB72FAAEAAA+4cAAB
  75288125          3 AAB72FAAEAAA+4cAAC
  75288125          9 AAB72FAAEAAA+4cAAD

SQL>

Not the need to commit to get the Row SCN. Now let us make soem further changes and use the ORA_ROWSCN to spot which rows have changed...

SQL> update t23
  2  set name = 'MR KNOX'
  3  where id = 2
  4  /

1 row updated.

SQL> insert into t23 values ('SALLY', 6)
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select ora_rowscn, id, rowid from t23
  2  where ora_rowscn > 75288125
  3  /

ORA_ROWSCN         ID ROWID
---------- ---------- ------------------
  75288182          2 AAB72FAAEAAA+4cAAB
  75288182          6 AAB72FAAEAAA+4cAAE

SQL>

ORA_ROWSCN was introduced in 10g. Find out more.

You will need a job to poll the table for changes. Also you need to build a table in which the polling job can use to store the max(ora_rowscn) for each run.