Is there any way to determine if the ORA_ROWSCN number in a table in Oracle is corrupted?
(Hyderabad, AP, India)
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 unlikely that it could be corrupted without there being major issues with the Oracle
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 querying sys.obj$
which is one of the data dictionary tables.SQL>CONNECT / AS SYSDBA
SQL>SELECT MIN(ORA_ROWSCN) FROM SYS.OBJ$;
SQL>SELECT ORA_ROWSCN FROM employees WHERE employee_id=188;
SQL>SELECT employee_id,last_name FROM employees WHERE ORA_ROWSCN=168313 AND ROWNUM <10;
102 De Haan
See the Oracle SQL Refererence
for more information about the ORA_ROWSCN pseudo-column.
For more help, there are a number of Oracle SQL tutorials
available on our site.
In conjunction with our partners, we also offer instructor-led Oracle training courses in the UK
, in the New York/Boston areas in the U.S
, as well as classroom-quality, on-line training courses