5

I need to run a method in Java every time a specific table of an Oracle DB is updated (any sorts of updates, including record additions, deletions and modifications).

What is the most efficient way do "poll" a table for changes from Java that has good performance and does not put too much pressure on the DB?

Unfortunately I have many constraints:

  • I can't create additional tables, triggers, stored procedures etc., because I have no control over the DB administration / design.
  • I'd rather avoid Oracle Change Notification, as proposed in that post, as it seems to involve C/JNI.
  • Just counting the records is not good enough as I might miss modifications and simultaneous additions/deletions.
  • a delay of up to 30/60s between the actual change and the notification is acceptable.
  • The tables I want to monitor generally have 100k+ records (some 1m+) so I don't think pulling the whole tables is an option (from a DB load / performance perspective).
Community
  • 1
  • 1
assylias
  • 321,522
  • 82
  • 660
  • 783

1 Answers1

5

Starting from oracle 11g you can use Oracle Change Notification with plain JDBC driver Link

Konstantin V. Salikhov
  • 4,554
  • 2
  • 35
  • 48
  • 1
    With an 11.2 client and a 9.2 database, I get an exception - it looks like the database has to be 10.2+ for this to work :-( – assylias May 29 '12 at 12:20