Capture changes using Oracle 10g's new row timestamps

by Bob Watkins | Jun 27, 2006 1:54:00 PM

Tags: Storage, Databases, Listing B, Bob Watkins, Oracle Application Server 10g, ORA_ROWSCN, SCN, Oracle Corp., Oracle Tips Newsletter

Takeaway: In Oracle 10g, a new pseudocolumn called ORA_ROWSCN is available on every row that approximates when the row was last updated. ORA_ROWSCN gives a "conservative upper bound" system change number for the transaction that last modified the row.

Data warehouses are often populated by multiple source systems, each running their own applications. It can be tricky to determine which rows were recently updated, so that feeds to the data warehouse only include the new data. This is especially a problem with legacy applications that do not include "date last updated" columns in their schemas. Companies are naturally reluctant to modify working code to add more columns just to track changes.

In Oracle 10g, a new pseudocolumn called ORA_ROWSCN is available on every row that approximates when the row was last updated. ORA_ROWSCN gives a "conservative upper bound" system change number for the transaction that last modified the row. This means that the SCN is an estimate because SCNs are tracked only at the block level by default in Oracle.

In Listing A, for example, the ORA_ROWSCN is selected on a small table, one row is updated, and then the ORA_ROWSCN is selected again. Even though only one row was changed, the rest indicate the new SCN as well. (For more precision, you can enable row-level SCN tracking when a table is first created. Unfortunately, you cannot ALTER the table to add that feature later.) So, if the number of blocks updated is low compared to the number of blocks in the table, this may be a way to detect changes without bringing along too many extra rows.

What if you need the date and time associated with that transaction? The function, SCN_TO_TIMESTAMP, can turn the ORA_ROWSCN into a timestamp value that you can query or use as a WHERE clause predicate. Again, however, the timestamp is an estimate.

ORA_ROWSCN is also a convenient way to get an SCN to use as a cutoff for flashback queries (although ORA_ROWSCN itself cannot be selected during a flashback). Instead, use a flashback versions query, and select the VERSIONS_STARTSCN and VERSIONS_ENDSCN pseudocolumns.

Listing B demonstrates a flashback query that uses an SCN one lower than current to obtain the prior value of a data row. Notice that King's salary is back to 5000, and the ORA_ROWSCN value is the original SCN.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Print/View all Posts Comments on this article

Pretty much useless earl.coombs@... | 06/28/06

Not necessarily bwatkins | 06/28/06