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.

Weekly Oracle tips in your inbox

TechRepublic’s free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.

Automatically sign up today!

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.