When was a table last changed?

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).

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

21 Responses to When was a table last changed?

  1. Tom says:

    Tanel,

    You are seriously the man. I swear, I love your blog.

    One thing I would like to add is that I think setting rowdependencies is that it will take an extra 6 bytes per row. Basically the same thing as having a char(6) to the table. A small price to pay and a great way to do auditing if you want to know when data changed.

  2. Martin says:

    Tanel,
    at least for very recent updates the function SCN_TO_TIMESTAMP would give a much more precise information concerning which timestamp is associated with the scn.
    If the scn is too far in the past, the function raises an exception, so here your approach would come in very handy.
    Best regards,
    Martin
    PS: Thank you again for your 2008-presentation at Austrian Oracle User Group together with Christian Antognini

  3. Boris says:

    Why don’t you use just SCN_TO_TIMESTAMP function?

  4. Tanel Poder says:

    Thanks Martin, Boris… Yep, SCN_TO_TIMESTAMP function would give better precision (and it is easier to use).

    But my approach works also on 9i, there’s no SCN_TO_TIMESTAMP function in 9i.

  5. Tanel Poder says:

    Correction, this script itself does not work on 9i as there’s no ORA_ROWSCN function in 9i if I recall correctly. I have used a script somewhat similar to lastchanged.sql in 9i where I manually paste in a SCN taken from datablock header.

    So, the only benefit of using v$log_history is that it may give you longer history than the scn_to_timestamp() does. Also, by querying smon_scn_time directly you’ll at least know when the changed happened *before* the earliest SCN to time record in smon_scn_time table (scn_to_timestamp function will return an exception if it doesnt find a matching record).

    I have uploaded an improved version of lastchanged.sql script to http://www.tanelpoder.com/files/scripts/lastchanged.sql

    It will now use the SCN_TO_TIMESTAMP() function first and only when its unable to resolve the SCN to time I run my other 2 queries to get the timestamp.

    Thanks for the suggestions…

    
    
    SQL> @lastchanged obj$ 1=1
    
    -- LastChanged.sql v1.0 by Tanel Poder ( http://www.tanelpoder.com )
    
    Running this query:
    
    .   select MAX(ora_rowscn)
    .   from obj$
    .   where 1=1;
    
    
    DATA_SOURCE       LAST_CHANGED
    ----------------- ----------------------------------------------------------------------
    scn_to_timestamp  2009-02-08 19:48:53
    
    51957 rows analyzed.
    SQL>
    SQL>
    SQL> @lastchanged rule$ 1=1
    
    -- LastChanged.sql v1.0 by Tanel Poder ( http://www.tanelpoder.com )
    
    Running this query:
    
    .   select MAX(ora_rowscn)
    .   from rule$
    .   where 1=1;
    
    
    DATA_SOURCE       LAST_CHANGED
    ----------------- ----------------------------------------------------------------------
    scn_to_timestamp  SCN_TO_TIMESTAMP couldn't convert SCN to time. See next section below
    
    DATA_SOURCE       LAST_CHANGED
    ----------------- ----------------------------------------------------------------------
    sys.smon_scn_time Before  2009-01-28 20:51:19 (earlier than 11 days ago)
    v$log_history     Between 2008-10-21 03:30:25 and 2008-10-21 16:00:58 (751 minute range)
    
    4 rows analyzed.
    
    
  6. Chris says:

    Another possibility is 11g’s Flashback Archive. My experience is that Oracle has integrated the 10g Flashback query, flashback versions query, and flashback_transaction_query view with the Flashback Archive. So, the information that is limited in 10g to the depth of undo data is limited in 11g only by the size of your flashback archive retention period.

    Of course, you have to create the Flashback archive and enable archiving for your tables first …

  7. Anand says:

    Hi Tanel.

    This was something i was looking from longtime.I was questioned for the last dml on 9i for which neither the audit was enabled nor monitoring was enabled.

    As, i think the lastest post that you have posted on point 5. works on 9i too.

    But when i am trying it on 9i it gives me an error
    {quote}
    Running this query:

    . select MAX(ora_rowscn)
    . from rule$
    . where 1=1;

    SP2-0310: unable to open file “C:\Documents.sql”
    old 4: FROM &1
    new 4: FROM and
    old 5: WHERE &2;
    new 5: WHERE Settings\db.admin\Desktop\anand\scripts\saveset;
    FROM and
    *
    ERROR at line 4:
    ORA-06550: line 4, column 11:
    PL/SQL: ORA-00903: invalid table name
    ORA-06550: line 2, column 6:
    PL/SQL: SQL Statement ignored
    {quote}

    One more thing that i wan’t to know is that what if i don’t want to give

  8. Tanel Poder says:

    Hi Anand,

    ORA_ROWSCN is available only from 10g as far as I know (I don’t have 9i db handy right now).

    So, you would need to
    1) dump the datablock containing the row of interest and get the scn from block header ( it looks something like this: “scn: 0×0000.016a75be” the scn is reported in hex )
    2) Run a query similar to my example which takes the SCN as a parameter instead of from ORA_ROWSCN

  9. Tanel Poder says:

    Btw, You can comment out the @@loadset and @@saveset commands from the script, if you’re not using my full TPT script set ( http://www.tanelpoder.com/files/TPT_public.zip )

  10. Anand says:

    Hi Tanel.

    Yes, ORA_ROWSCN is not available in 9i.In 9i auditing and table monitoring are the 2 possible ways of getting the last dml as far as i know.

    Secondly, suppose that i want don’t want to use in
    @laschanged scott.emp then should i use 1=1 in

    like ,
    @lastchanged scott.emp 1=1

  11. Tanel Poder says:

    …or you can dump the datablock and get the SCN from block header, that’s exactly where ORA_ROWSCN gets the SCN from if you don’t have rowdependencies enabled.

    lastchanged.sql always requires two parameters, table name and the rows to be examined. When you use 1=1 then all rows are examined and you get the latest time of any row changed in the table.

    But instead of 1=1 you can use where ID=xyz or whatever other condition, in this case the script reports when was a last change done to any of the rows matching the where condition..

    If you look into script header, there are use cases and explanation in there…

  12. Achal says:

    Hi,

    My name is Achal Kumar and i work as Oracle DBA.

    I am trying to use your query and i am getting these below errors. I am trying to find out: When the Table was Updated / Modified:

    Below is the error that i am getting, Please help me out.

    SQL> @lastchange.sql obj$ name=’IM_CONFIGURATION’

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

    Running this query:

    . select MAX(ora_rowscn)
    . from obj$
    . where name=’IM_CONFIGURATION’;

    SP2-0310: unable to open file “saveset.sql”
    old 4: FROM &1
    new 4: FROM obj$
    old 5: WHERE &2;
    new 5: WHERE name=’IM_CONFIGURATION’;

    DATA_SOURCE
    —————–
    LAST_CHANGED
    ——————————————————————————–
    scn_to_timestamp
    2010-09-22 11:29:18

    old 11: FROM &1
    new 11: FROM obj$
    old 12: WHERE &2
    new 12: WHERE name=’IM_CONFIGURATION’
    old 96: &runme WHERE 1=0
    new 96: WHERE 1=0
    old 1: SELECT COUNT(*)||’ rows analyzed.’ FROM &1 WHERE &2
    new 1: SELECT COUNT(*)||’ rows analyzed.’ FROM obj$ WHERE name=’IM_CONFIGURATION’

    2 rows analyzed.
    SP2-0310: unable to open file “loadset.sql”

  13. RAJUKR says:

    Awe some Tanel, Thanks for your great help .

  14. Girish Sharma says:

    Hi,

    I was just mentioning the link of this page for one of the question at OTN forum :
    http://forums.oracle.com/forums/thread.jspa?threadID=2230456&tstart=0
    where OP is asking the last DML in 9i. I just clicked the http://www.tanelpoder.com/files/scripts/lastchanged.sql link, but it is not opening. Can you please paste the code of script; so that :
    1.I may point that OTN OP to this page.
    2.If any time due to any reason, the link for SQL is not working; visitor may get it from here itself.
    3.I am not sure, you have incorporated 9i’s limitation for this i.e. in 9i there is no ORA_ROWSCN; don’t know how earlier DBAs i.e. prior to 9i (early 1990s); have managed this answer though!

    Best Regards
    Girish Sharma

  15. Tanel Poder says:

    @Girish Sharma

    For now please download my scripts from tpt_public.zip from here:

    http://tech.e2sn.com/oracle-scripts-and-tools

    All my scripts are there.
    Some day, when I have time, i’ll fix the broken links…

  16. Girish Sharma says:

    Hi,
    Thanks for reply and link.

    I will keep visiting your blog in hope that you will fix the broken link with 9i supports.

    Best Regards
    Girish Sharma

  17. Girish Sharma says:

    Hi,

    Just Re-requesting for last DML in 9i.
    Sir, I am one of the person who daily read your blog.

    Best Regards
    Girish Sharma

  18. Tanel Poder says:

    @Girish Sharma

    The link above your first post has all my scripts in it – just download and unzip it for now! Some day I’ll fix the broken links too :)

  19. Rishwinger says:

    Hi Tanel,

    Q)How to find out when was a table structure last modified?

    When we use alter to modify table structure we get it in user_objects.last_ddl_time but when we modify table structure using online redefinition then where to look which table structure was last modified?

    Thanks in advance

  20. Gus Spier says:

    Tanel, it is unclear to me how to convert a value like “scn: 0×0000.016a75be” to a decimal system commit number? Google doesn’t seem to have the answer. Oracle documents remain pretty opaque. is this documented anywhere?

    Thanks,

    Gus

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>