Can you use ORA_ROWSCN to determine when was the last modification of a row?

Introduction:
ORA_ROWSCN is a pseudo column that was introduced in Oracle 10g.
This pseudo column will display the SCN of the last time that a row or a the block (in which the row resides) was modified.
You can later use to SCN_TO_TIMESTAMP function to translate the SCN to a timestamp.

Let's see an exmaple:

SQL> create table test (id number, name varchar2(20));
Table created.

SQL> insert into test values (1, 'ABC');
1 row created.

SQL> insert into test values (2, 'XYZ');
1 row created.

SQL> commit;
Commit complete

SQL> update test set name='DEF' where id=1;

SQL> commit;
Commit complete

SQL> select ora_rowscn,scn_to_timestamp(ora_rowscn),id,name from test;

ORA_ROWSCN     TO_TIMESTAMP(ORA_ROWSCN)                   ID           NAME
-----------------   ---------------------------------------  --       --------
  95752977               24-AUG-15 06.45.33.000000000 PM                   1            DEF
  95752977               24-AUG-15 06.45.33.000000000 PM                   2            XYZ

As you can see, the scn of both rows is the same. This is because the table was created without the ROWDEPENDENCIES clause, which ensures that every row in a table has its own SCN, therefore the ORA_ROWSCN displays the scn of the last time the the block was modified.

Let's create the table but this time with the ROWDEPENDENCIES clause:


SQL> drop table test;
Table dropped.

SQL> create table test (id number, name varchar2(20)) ROWDEPENDENCIES;

Table created.

SQL> insert into test values (1, 'ABC');
1 row created.

SQL> insert into test values (2, 'XYZ');
1 row created.

SQL> commit;
Commit complete

SQL> update test set name='DEF' where id=1;

SQL> commit;
Commit complete

SQL> select ora_rowscn,scn_to_timestamp(ora_rowscn),id,name from test;

ORA_ROWSCN     TO_TIMESTAMP(ORA_ROWSCN)                   ID           NAME
-----------------   ---------------------------------------  ---       --------
  95755953               24-AUG-15 07.01.46.000000000 PM                   1            DEF
  95755923               24-AUG-15 07.00.14.000000000 PM                   2            XYZ


In this case, because the table was created with the ROWDEPENDENCIES clause therefore each row has its own SCN and not the same SCN of the last time that the block was modified.
Does it mean that you can use ORA_ROWSCN to track the times of modifications for the rows in your tables? 

The answer is no, because the SCN_TO_TIMESTAMP information is limited to SCN of the last 5 days, and if you will try to translate SCN which is older than the last 5 days you'll get an error:
SQL> select scn_to_timestamp(78199991) from dual;
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

select scn_to_timestamp(78199991) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number

Conclusions:
ORA_ROWSCN is a nice pseudo-column but you need to be aware of its limitations:

1. Unless you specify the ROWDEPENDENCIES clause in the creation of the table, it will report on the last SCN of the block modification and not the row modification.

1. SCN_TO_TIMESTAMP can translate SCN to Timestamps of the last 5 days only.

The title of this article is "Can you use ORA_ROWSCN to determine the last modification of a row?" 
The answer is: no
You'll have to track the change date yourself. I would recommend using one of the following approaches:

• LogMiner
• Auditing