Oracle ora_rowscn and the rowdependencies clause Oracle Tips by Burleson Consulting

 

When managing web-based Oracle databases, the traditional "select for update" locking is inappropriate, and Oracle professionals have struggled with alternative mechanisms to maintain data integrity:

• Re-read - When an update comes in from the internet, the Oracle code re-reads the data to ensure that there are no changes since the data was originally delivered.
   

• Timestamp - Some Oracle shops add a timestamp column to enforce serial updates and prevent accidental overlaying of data.

Now in Oracle 10g we see the new rowscn pseudo-column and the rowdependencies clause.  The ora_rowscn provides the System Change Number (SCN) associated with each individual row in a table when it was read. One thing to note, by default Oracle will not track SCN's at the row level in a table, so you need to enable row level dependency tracking by using the ROWDEPENDENCIES keyword in the CREATE TABLE command.

 

create table customer (

  cust_id     number(10) not null,
  cust_name   varchar2(50) not null

ROWDEPENDENCIES

tablespace users;

 

This rowdependencies clause changes the default behavior of Oracle to put an SCN on every row within the tables, and not just one SCN for every physical data block.

 

For more details on using ora_rowscn and the rowdependencies clause, see the book "Oracle 10g New Features".