Last Modified

Have you ever wondered if there’s a way to see when the data in a table was last modified ? Depending on what you’re really trying to achieve, how accurate you want the answer to be, what resources you’re prepared to use, and how you’ve configured your database, you may be able to get an answer that’s good enough very easily.

If all you want is a rough indication that the table hasn’t changed over the last few days, or weeks, or even months, you may be able to run a simple, but potentially brutal, query against the table to find out. Here’s an example that get’s there in three steps – using the table sys.link$ as a target. Steps 1 and 2 are for explanatory purposes only.

select
	ora_rowscn
from
	link$
;

select
	first_change#, first_time
from
	v$log_history
order by
	first_change#;

select
	min(first_time)
from
	v$log_history
where
	first_change# >= (
		select max(ora_rowscn) from link$
	)
;

The pseudo-column ora_rowscn is an upper bound for the SCN at which the last change to a row committed. If you have enabled rowdependencies this will generally be the actual commit SCN for the row, otherwise Oracle has various algorithms for working out the largest SCN avaiable that is not less than the SCN at which the transaction committed. If all you’re interested in is an indication of when the table last changed even this is upper bound SCN may be good enough.

Step 2 is there to remind you that v$log_history records the SCN and timestamp for the first change in the file. This gives us a very crude conversion between SCNs and timestamps.

The final step gives us the answer we want. We find the approximate SCN of the most recent change to the table, and then report the timestamp of the first log file that started at a higher SCN.

Here’s a sample of output from my laptop for the three queries above:

ORA_ROWSCN
----------
   1274633

FIRST_CHANGE# FIRST_TIM
------------- ---------
       969527 27-APR-10
       976983 27-APR-10
       980116 27-APR-10
       988729 27-APR-10
       995910 27-APR-10
      1012055 27-APR-10
      1057428 28-APR-10
      1100463 30-APR-10
      1157543 03-MAY-10
      1188215 03-MAY-10
      1220173 04-MAY-10
      1226864 05-MAY-10
      1261756 06-MAY-10
      1300091 12-MAY-10	-- this is the critical row
      1342594 16-MAY-10
      1364917 16-MAY-10
      1418975 18-MAY-10
      1492902 19-MAY-10
      1582039 23-MAY-10
      1593611 09-JUN-10
      1596840 09-JUN-10

MIN(FIRST
---------
12-MAY-10

Remember – this isn’t trying to be accurate, and it’s not trying to guarantee that a table hasn’t been used recently, it’s just trying to say “it  looks as if this table may not have changed recently”. But if you’re trying to clear old garbage out of a production system this gives you one more piece of evidence about whether it might safe to drop a specific table. (As a simple example of why it’s not conclusive evidence – think about a table of currency codes: it’s likely to be very useful in a banking system, but may have been unchanged for years.)