select last rows

I just read about a query to retrieve last modification row of a date


SQL> SELECT ora_rowscn FROM tab_test;

ORA_ROWSCN
----------
351744
351744
351744
351744
351744
351744
6 rows selected.

SQL> UPDATE tab_test SET valeur=valeur*1.1 WHERE col_id=1;

3 rows updated.

SQL> commit;

Commit complete

SQL> SELECT ora_rowscn FROM tab_test:

ORA_ROWSCN
----------

351744
351744
351744
371423
371423
371423

6 rows selected.

conclusion of the author : very neat to retrieve last modification date.

Well, I am worried. I have answered so many times on the technical forums here for example, that the only way to retrieve the last rows is to use a date column, and to manually update it (or with a trigger or a default value for insert) with the last modification date.

What should I do?
test it!


14:52:07 SQL> create table t as
select rownum x from all_objects;

Table created.

14:56:23 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 4238

let’s update one row


14:54:12 SQL> update t set x=-1 where rownum=1;

1 row updated.

14:55:04 SQL> commit;

Commit complete.

14:58:03 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 3580
04.08.2006 14:57:14.00 658

what? I updated one row, why did it updated so many rows? Let’s look at the block

14:58:16 SQL> select dbms_rowid.rowid_block_number(rowid) block_number,scn_to_timestamp(ora_rowscn) timestamp,count(*) from t group by dbms_rowid.rowid_block_number(rowid),scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

BLOCK_NUMBER TIMESTAMP COUNT(*)
------------ ---------------------- ----------
651 04.08.2006 14:56:23.00 658
652 04.08.2006 14:56:23.00 658
653 04.08.2006 14:56:23.00 658
654 04.08.2006 14:56:23.00 658
655 04.08.2006 14:56:23.00 658
656 04.08.2006 14:56:23.00 290
650 04.08.2006 14:57:14.00 658 <=====================

ok, what has the doc to say about this :

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking

Well, let's try again with row dependencies.


15:04:53 SQL> drop table t;

Table dropped.

15:04:55 SQL> create table t rowdependencies as select rownum x from all_objects;

Table created.

15:05:28 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4241

15:05:42 SQL> update t set x=-1 where rownum=1;
1 row updated.

15:05:58 SQL> commit;

Commit complete.

15:06:09 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4240
04.08.2006 15:06:08.00 1

sounds better! probably not 100% reliable but way better, it seems almost usable ...

5 thoughts on “select last rows

  1. Thomas Kyte

    also, it only works for rows updated relatively recently

    sys%ORA10GR2> select min(ora_rowscn) from obj$;

    MIN(ORA_ROWSCN)
    —————
    18349

    sys%ORA10GR2> select scn_to_timestamp(18349) from dual;
    select scn_to_timestamp(18349) from dual
    *
    ERROR at line 1:
    ORA-08181: specified number is not a valid system change number
    ORA-06512: at “SYS.SCN_TO_TIMESTAMP”, line 1

  2. Laurent Schneider

    yes, thanks, and also not for uncommited rows if the table has rowdependencies!

    SCOTT@LSC01> update t set x=0 where rownum=1;

    1 row updated.

    SCOTT@LSC01> select ora_rowscn from t where x=0;
    ORA_ROWSCN
    ———-

  3. Simon Hunt

    Hi Laurent,

    I am trying to implement ORA_ROWSCN to control the optimistic locking on my app. Is it possible to enable row dependencies on an existing table? or will I need to do an online rebuild?

    Simon

  4. Pingback: Find latest change in table having no date/time column | Database Store

Comments are closed.