ORA_ROWSCN, SCN_TO_TIMESTAMP and ROWDEPENDENCIES

In one of the application that I work, the developers used the ORA_ROWSCN pseudo column to filter records for deletion as below and got an error as well.

delete from FOCUS_TRANS where scn_to_timestamp(ora_rowscn) < sysdate - :1

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
08181. 00000 - "specified number is not a valid system change number"
*Cause: supplied scn was beyond the bounds of a valid scn.
*Action: use a valid scn.

Though I'm not a big fan of using pseudo columns for record selection criteria, I thought of doing a bit of study on the same. Here are the things that one should think about when using ORA_ROWSCN

ORA_ROWSCN reflects the system change-number (SCN) of the most recent change to a row. The granularity is of 2 levels, block (coarse) or at row (fine-grained). Let's come to this part later. You may get details on ORA_ROWSCN from here.

Now, why does the query fail with the error? SCN should be stored for all the records which where either inserted or updated and it shouldn't be a problem for the query to convert that scn to timestamp using SCN_TO_TIMESTAMP function, isn't it?

Here is the catch on SCN_TO_TIMESTAMP: Note from Oracle Documentation

"The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old."

The above means Oracle can keep track of times up to a maximum of 5 days or tuned_undoretention if the database is in automatic undo management. When the scn number becomes more than this specification, it goes out of range for the scn_to_timestamp function and thus the error.

Let's see which record causing the error in the database

I'm going to find which is the minimum scn that the database currently has in it's memory for the conversion.

SQL> select min(scn) min_scn from sys.SMON_SCN_TIME;

        MIN_SCN
---------------
 13777748906536

So anything beyond this scn should throw us the error.
In the table, here are the details.

SQL> select * from (select ora_rowscn, customer_number from FOCUS_TRANS
  2  where ora_rowscn < 13777748906536 order by ora_rowscn desc) where rownum < 6;

     ORA_ROWSCN CUSTOMER_NUMBER
--------------- --------------------
 13777629123985 00000000000019982791
 13777629123983 00000000000023280153
 13777572276494 00000000000023280145
 13777572273829 00000000000023283108
 13777572273825 00000000000023283108

SQL>

Now this first value which is beyond the minimum scn should throw error when attempted to convert to timestamp.

SQL> select ora_rowscn, scn_to_timestamp(ora_rowscn), customer_number, insert_date_time from FOCUS_TRANS
  2  where ora_rowscn = 13777629123985;
select ora_rowscn, scn_to_timestamp(ora_rowscn), customer_number, insert_date_time from FOCUS_TRANS
                   *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


SQL>

So we found which record or which records (out of range) are the cause of this error. If you wonder if the previous value within the range would work, let's try that too

SQL> select * from (select ora_rowscn, customer_number from FOCUS_TRANS
  2  where ora_rowscn >= 13777748906536 order by ora_rowscn) where rownum < 3;

     ORA_ROWSCN CUSTOMER_NUMBER
--------------- --------------------
 13777817297849 00000000000024408803
 13777983336842 00000000000024855932

SQL> select ora_rowscn, scn_to_timestamp(ora_rowscn), customer_number, insert_date_time from FOCUS_TRANS
  2  where ora_rowscn = 13777817297849;

     ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                CUSTOMER_NUMBER      INSERT_DA
--------------- --------------------------------------------------------------------------- -------------------- ---------
 13777817297849 14-APR-18 11.59.33.000000000 AM                                             00000000000024408803 19-DEC-17

SQL>

That worked :)

Now coming back to why I don't want the developers to use ORA_ROWSCN for selection criteria in this case is as below.

First:
This solution works only when the insert, update and delete all occurs within 5 days (or lets say within the time reached tuned_undoretention) else it would fail and you can't do anything about that.

Second:
To implement this solution, the table should be created with ROWDEPENDENCIES for ORA_ROWSCN to be working with row level granularity.

Let's take this example below to show how ROWDEPENDENCIES work. See the difference in create table statements highlighted and how scn_to_timestamp getting converted with enabling ROWDEPENDENCIES

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

Table created.

SQL> insert into rowscn_test values (1, 'Selva');

1 row created.

SQL> insert into rowscn_test values (2, 'Kumar');

1 row created.

SQL> commit;

Commit complete.


SQL> select ora_rowscn, id, name from rowscn_test;

ORA_ROWSCN         ID NAME
---------- ---------- --------------------
6.8198E+10          1 Selva
6.8198E+10          2 Kumar

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

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
        ID NAME
---------- --------------------
25-APR-18 06.36.07.000000000 AM
         1 Selva

25-APR-18 06.36.07.000000000 AM
         2 Kumar


SQL> update rowscn_test set name='SELVA' where id=1;

1 row updated.

SQL> commit;

Commit complete.

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

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
        ID NAME
---------- --------------------
25-APR-18 06.37.49.000000000 AM
         1 SELVA

25-APR-18 06.37.49.000000000 AM
         2 Kumar


SQL> create table rowscn_test_rld (id number, name varchar2(20)) rowdependencies;

Table created.

SQL> insert into rowscn_test_rld values (1, 'Selva');

1 row created.

SQL> insert into rowscn_test_rld values (2, 'Kumar');

1 row created.

SQL> commit;

Commit complete.

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

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
        ID NAME
---------- --------------------
25-APR-18 06.42.19.000000000 AM
         1 Selva

25-APR-18 06.42.19.000000000 AM
         2 Kumar


SQL> update rowscn_test_rld set name='SELVA' where id=1;

1 row updated.

SQL> commit;

Commit complete.

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

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
        ID NAME
---------- --------------------
25-APR-18 06.42.52.000000000 AM
         1 SELVA

25-APR-18 06.42.19.000000000 AM
         2 Kumar

Now you can see that while designing the table, if the table is created without specifying ROWDEPENDENCIES, the ORA_ROWSCN selection will pick up records which doesn't fall into the selection criteria as well. In my case, the table is created with ROWDEPENDENCIES :)


Hope this post helps understanding the terms ORA_ROWSCN and ROWDEPENDENCIES better.

Happy SCNing !!! :)