I frequently get a question about how to find out when was a table last modified. I’m talking about table data, not table structure, the latter would be detectable from dba_objects.last_ddl_time.

Unless you have some table level DML auditing already turned on, then as one option you could use LogMiner and “just” work through the redo/archivelogs in the range of interest with it. This could be very time consuming (especially if the last change was done a while back), so here’s another option for getting last table modification info very easily, however it comes with some restrictions.

http://www.tanelpoder.com/files/scripts/lastchanged.sql

(read instructions and limitations from the script header).

The idea is following:

1. Oracle has an ORA_ROWSCN pseudocolumn which reports the last known change time for a row in a table. The “time” shows a commit SCN number of last transaction modifying the row, not a real timestamp though. It is important to note that unless the ROWDEPENDECIES are enabled, then the last SCN is known only at data block level, not row level, rowscn’s for all rows in a block would report whatever SCN is in the last change SCN in block header.
2. SCN is ever-increasing internal “time” used by Oracle recovery and transaction layers and it is possible to map this to real time with reasonable accuracy using few Oracle’s tables which store SCN to wallclock time mappings. My script reports the time range in which the last change to datablock/row occurred so you’ll know how accurate it is.
   I use sys.smon_scn_time and v$log_history views for SCN to real time mapping. So my script reports two times, first one may be more accurate, second one has longer history on the other hand.You can use whatever other datasource for doing this mapping, as long as it has SCNs and corresponding timestamps in it. For example, if you have log_checkpoints_to_alert parameter set to true, you can grep the SCN/timestamp pairs out with command like this:

   cat alert_win10g.log | egrep -e "^Beginning.*checkpoint|[[:alpha:]]{3} [[:alpha:]]{3} [[:digit:]]{2} "
   
   

Here are few usage examples.

First I’ll check some data which was probably last changed a long time ago:

SQL> @lastchanged sys.obj$ name='DBMS_STANDARD'

-- LastChanged.sql v1.0 by Tanel Poder ( http://www.tanelpoder.com )

Running this query:

.   select MAX(ora_rowscn)
.   from sys.obj$
.   where name='DBMS_STANDARD';

DATA_SOURCE       LAST_CHANGED
----------------- -------------------------------------------------------
sys.smon_scn_time Before  2008-12-31 16:05:25 (earlier than 21 days ago)
v$log_history     Before  2008-10-27 03:58:16 (earlier than 86 days ago)

2 rows analyzed.

As both sys.smon_scn_time and v$log_history don’t have records dating back to that old time when the database was created (when DBMS_STANDARD and other object records in that datablock were created), they just show that the change happened before the oldest SCN to time mapping they have.

Here’s a check on a regular table, but we are only interested in rows/datablocks where the “sal” column is bigger than 1000:

SQL> @lastchanged scott.emp sal>1000

-- LastChanged.sql v1.0 by Tanel Poder ( http://www.tanelpoder.com )

Running this query:

.   select MAX(ora_rowscn)
.   from scott.emp
.   where sal>1000;

DATA_SOURCE       LAST_CHANGED
----------------- ---------------------------------------------------------------------
sys.smon_scn_time Between 2009-01-21 08:56:57 and 2009-01-21 09:02:19 (5 minute range)
v$log_history     Between 2009-01-21 08:26:43 and 2009-01-21 08:57:14 (31 minute range)

14 rows analyzed.

Lets change something (and commit too, as ORA_ROWSCN is updated at commit time, any non-committed change doesn’t really exist yet).

SQL> update scott.emp set sal = sal * 2;

14 rows updated.

SQL> commit;

Commit complete.

Let’s see the last change time again:

SQL> @lastchanged scott.emp sal>1000

-- LastChanged.sql v1.0 by Tanel Poder ( http://www.tanelpoder.com )

Running this query:

.   select MAX(ora_rowscn)
.   from scott.emp
.   where sal>1000;

DATA_SOURCE       LAST_CHANGED
----------------- ---------------------------------------------------------------
sys.smon_scn_time After   2009-01-21 09:02:19 (between 6 minutes ago and now)
v$log_history     After   2009-01-21 08:57:17 (between 11 minutes ago and now)

14 rows analyzed.

As you see, the last change time range has changed.

So, this script can be very useful first round diagnosis tool when you want to know when some table/data was changed the last (in case you don’t have fine grained auditing enabled already). That would help you to correctly determine if some data/row has not been modified since some time in past and also it can show you when some data/row has been modified in past, but this approach is subject to false positives if rowdependencies option is not enabled for that table.

Nevertheless, even with those false positives, this approach gives you the information which archivelogs you would need to study to find the latest changes to the data (instead of starting to mine through all the archivelogs generated in past in hope to find the change).

Tanel Poder
Administration, Oracle, Tools, Troubleshooting