sql - Oracle: How to find the timestamp of the last update (any table) within a schema? -

there oracle database schema (very small in data, still 10-15 tables). contains sort of configuration (routing tables).

there application have poll schema time time. notifications not used.

if no data in schema updated, application should use current in-memory version.

if table had update, application should reload tables memory.

what effective way check whole schema update since given key point (time or transaction id)?

i imagined oracle keeps transaction id per schema. there should way query such id , keep compare @ next poll.

i've found question, such pseudo-column exists on row level:

how find out when oracle table updated last time

i think similar exists on schema level.

can please point me in right direction?

i'm not aware of such functionality in oracle. see below.

the best solution can come create trigger on each of tables updates one-row table or context current date/time. such triggers @ table-level (as opposed row-level), wouldn't carry overhead triggers.

incidentally, oracle can't keep transaction id per schema, 1 transaction affect multiple schemas. might possible use v$ views track transaction objects affected, wouldn't easy , perform poorer trigger scheme.

it turns out, if have 10g, can use oracle's flashback functionality information. however, you'd need enable flashback (which carries overhead of it's own) , query ridiculously slow (presumably because it's not intended use):

select max(commit_timestamp)  flashback_transaction_query  table_owner = 'your_schema'        , operation in ('insert','update','delete','merge')  

---

in order avoid locking issues in "last updated" table, you'd want put update procedure uses autonomous transaction, such as:

create or replace procedure log_last_update pragma autonomous_transaction; begin    update last_update set update_date = greatest(sysdate,update_date);    commit; end log_last_update; 

this cause application serialize degree: each statement needs call procedure need wait until previous 1 finishes. "last updated" table may out of sync, because update on persist if update activated trigger rolled back. finally, if have particularly long transaction, application pick new date/time before transaction completed, defeating purpose. more think this, more seems bad idea.

---

the better solution avoid these issues insert row triggers. not lock table, there wouldn't serialization , inserts wouldn't need made asynchronously, rolled along actual data (and wouldn't visible application until data visible well). application max, should fast if table indexed (in fact, table ideal candidate index-organized table). downside you'd want job runs periodically clean out old values, didn't grow large.