ORA_ROWSCN
ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is helpful for to know approximately when the rows was last update. It is not absolutely accurate, because Oracle tracks SCNs by transaction committed for the block in which the row resides. It is possible to get more fine-grained approximation of the SCN by creating row-level dependency tracking.
Example:
SQL>create table emp(id number,name char(2000 char));
Table created.
SQL>insert into emp values(1,'Robin');
1 row created.
SQL>insert into emp values(2,'Thomas');
1 row created.
SQL>commit;
Commit complete.
SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;
SQL> insert into emp values(3,'Munir');
1 row created.
SQL> insert into emp values(4,'Moon');
1 row created.
SQL> insert into emp values(5,'Suny');
1 row created.
SQL>commit;
Commit complete.
SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;
From the above table you can see when we inserted new row, id 3 its updated ORA_ROWSCN for all the rows that resides in same block. So it’s not possible to get accurate result when the row was updated, since if any other row insert/update in same block then its change ORA_ROWWSCN number for the every row in same block.
SQL>update emp set name='DON' where id=1;
1 row updated.
SQL>commit;
Commit complete.
SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;
Similar things we can see here, we update row for id 1 and it’s updated all the ORA_ROW_SCN number for other rows as well that resides in the same block.
We can get more fine-grained approximation of the SCN by creating table with row-level dependency tracking. There is no additional performance issue we noticed by enabling this features but it’s always better to test it before implanting anything in production environment.
Example:
SQL>create table emp(id number,name char(2000 char)) rowdependencies;
Table created.
SQL>create table emp(id number,name char(2000 char));
Table created.
SQL>insert into emp values(1,'Robin');
1 row created.
SQL>insert into emp values(2,'Thomas');
1 row created.
SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;
SQL> insert into emp values(3,'Munir');
1 row created.
SQL> insert into emp values(4,'Moon');
1 row created.
SQL> insert into emp values(5,'Suny');
1 row created.
SQL>commit;
We can see here new id 3 added in same block but the ORA_ROWSCN number is different after creating the table with rowdependiencies.
SQL>update emp set name='DON' where id=1;
1 row updated.
SQL>commit;
Commit complete.
Similar things we can see here id 1 is updated and it’s updated the ORA_ROWSCN number only for that rows not the rest of the rows in the same block.