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 ...
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
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
———-
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
there is not ALTER TABLE, you need to create a new table, with online redefinition if you want
Pingback: Find latest change in table having no date/time column | Database Store