Is there any way to determine if the ORA_ROWSCN number in a table in Oracle is corrupted?

Retrieving min(ora_rowscn) in Oracle

 

I have found that the ORA_ROWSCN number for some of the records in a table in my Oracle database is showing as 1, but when I ran a query with the condition "ORA_ROWSCN = 1", no rows are returned.

ORA_ROWSCN is a pseudo column (like ROWNUM) in Oracle databases and returns the system change number (SCN) of the most recent change to the row in the current session. The SCN acts as a kind of timestamp in Oracle databases and provides a unique identifier for each committed transaction that enables read consistency of data. This number always increases. Every time a transaction is committed the SCN increases.

By default when a table is created the ORA_ROWSCN is actually the SCN of the block in which the row resides i.e. all rows in the same block will have the same SCN.

As ORA_ROWSCN is only a pseudo-column and by default the SCN is only tracked at block level it seems very unlikely that it could be corrupted without there being major issues with the Oracle database which would almost certainly render it completely unusable.


It is also almost impossible for the SCN to have a value of 1 even for a newly created empty database as database creation involves creating at least the system tablespace which contains the data dictionary and other objects needed for an Oracle database to run. In which case, obviously, querying a table for rows meeting the condition ORA_ROWSCN = 1 would return no rows.

You can determine the oldest SCN in your database by queryingsys.obj$ which is one of the data dictionary tables.

SQL>CONNECT / AS SYSDBA
connected
SQL>SELECT MIN(ORA_ROWSCN) FROM SYS.OBJ$;

MIN(ORA_ROWSCN)
---------------
          12743

SQL>connect hr/hr
connected
SQL>SELECT ORA_ROWSCN FROM employees WHERE employee_id=188;

ORA_ROWSCN
----------
   168313

SQL>SELECT employee_id,last_name FROM employees WHERE ORA_ROWSCN=168313 AND ROWNUM <10;

EMPLOYEE_ID LAST_NAME
----------- ---------
        100 King
        101 Kochhar
        102 De Haan
        103 Hunold
        104 Ernst
        105 Austin
        106 Pataballa
        107 Lorentz
        108 Greenberg