There is an Oracle database schema (very small in data, but still about 10-15 tables). It contains a sort of configuration (routing tables).
There is an application that have to poll this schema from time to time. Notifications are not to be used.
If no data in the schema were updated, the application should use its current in-memory version.
If any table had any update, the application should reload all the tables into memory.
What would be the most effective way to check the whole schema for update since a given key point (time or transaction id)?
I am imagined Oracle keeps an transaction id per schema. Then there should be a way to query such an ID and keep it to compare with at next poll.
I've found this question, where such an pseudo-column exists on a row level:
How to find out when an Oracle table was updated the last time
I would think something similar exists on a schema level.
Can someone please point me in the right direction?