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

by Rakesh
(Hyderabad, AP, India)

Retrieving min(ora_rowscn) in Oracle

Retrieving min(ora_rowscn) in Oracle

Retrieving min(ora_rowscn) in Oracle
Selecting ora_rowscn from employees table

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 database.

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
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


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.

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

Average Rating starstarstarstarstar

Click here to add your own comments

May 11, 2011
Rating
starstarstarstarstar
patch for Corrupted Ora_RowSCN
by: Rakesh

Related to corruption of ORA_ROWSCN is a defect in Oracle10g. Oracle released a patch 11731126 to fix this issue. After applying this patch we did not find any corrupted records in Oracle DB.

Defect id 6157713 - KTBGDS0() DOES NOT RETURN DEPENDENT SCN FOR INDEX BLOCK THAT HAS BEEN SPLIT

Feb 02, 2011
Rating
starstarstarstarstar
More on using ORA_ROWSCN
by: Solomon Yakobson

Before writing queries using ORA_ROWSCN you should understand ORA_ROWSCN behavior is dependent on ROWDEPENDENCIES. I'll give you an example:

SQL> select deptno,ora_rowscn from dept;

DEPTNO ORA_ROWSCN
---------- ----------
10 529426
20 529426
30 529426
40 529426

SQL> update dept set deptno=deptno;

4 rows updated.

SQL> select deptno,ora_rowscn from dept;

DEPTNO ORA_ROWSCN
---------- ----------
10 529426
20 529426
30 529426
40 529426

SQL> commit;

Commit complete.

SQL> select deptno,ora_rowscn from dept;

DEPTNO ORA_ROWSCN
---------- ----------
10 13706784
20 13706784
30 13706784
40 13706784

SQL> create table dept1 rowdependencies as select * from dept;

Table created.

SQL> select deptno,ora_rowscn from dept1;

DEPTNO ORA_ROWSCN
---------- ----------
10 13706870
20 13706870
30 13706870
40 13706870

SQL> update dept1 set deptno=deptno;

4 rows updated.

SQL> select deptno,ora_rowscn from dept1;

DEPTNO ORA_ROWSCN
---------- ----------
10
20
30
40

SQL> commit;

Commit complete.

SQL> select deptno,ora_rowscn from dept1;

DEPTNO ORA_ROWSCN
---------- ----------
10 13706906
20 13706906
30 13706906
40 13706906

SQL>

As you can see, if table is created/altered with rowdependencies, actual ORA_ROWSCN is null for updated/inserted but not yet committed rows. Therefore, for such table your query:

select count(*) from emp where ORA_ROWSCN < dbms_flashback.get_system_change_number;

in general might not return same result as

select count(*) from emp;

SY.

Jan 21, 2011
Rating
starstarstarstarstar
ORA_ROWSCN
by: Rakesh

I have run the following queries against the emp table in my Oracle database:
1. select count(*) from emp;
2. select count(*) from emp where ORA_ROWSCN <dbms_flashback.get_system_change_number;

Can you please explain should these queries should return the same number of records. As I am getting different count for these two queries. Does it indicate any corruption?


The only way these queries could return different results is if some of the rows could have an Oracle system change number >= to the current system change number. As that's not possible (as the system change number never goes down) it would suggest corruption in your database.

You could check what SCNs are stored in the table's blocks simply by running this query:
select distinct ora_rowscn from emp;

Obviously if there is another session updating the emp table (and committing changes) whilst you are counting the rows in it then this would cause the 2 counts to differ.

If you suspect corruption in your Oracle database you could run DBMS_REPAIR.CHECK_OBJECT specifying the schema name and the table name. This will return a count of the number of corruptions detected on the table.

See Oracle Database PL/SQL Packages and Types Reference for the details of using this procedure and Oracle Database Administrator's Guide for more information about fixing corruption in the database.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.