Notification of Oracle table changes in Java
java, oracle
Solution
Starting from oracle 11g you can use Oracle Change Notification with plain JDBC driver Link
Problem
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).