Tracking row changes with oracle10g version of the backtracking query

One of the trickiest problems with fault checking is the interaction between applications that access the same data. Although each application is inherently well-behaved, individual applications may make different assumptions about the data. Therefore, the line may appear, change, and disappear when you least expect it.

In the past, the solution to this type of problem was to discard the data when running two programs to track what was going on. The advent of Log miner makes it easier to perform this task, but it is more cumbersome to use. Now, in Oracle 10g, there is a tool with the same functionality as log miner, but it is more convenient to perform.

This tool, called a retrospective version query, relies on the automatic undo management feature and the undo tablespace to provide the line image from start to finish. After the from table name, before the table alias, the backtracking version query syntax proves the eligibility of the table name by indicating which row versions to include in the Select. Its syntax is:

Versions BETWEEN {SCN | TIMESTAMP}

{EXP | MinValue} and {exp | MAXVALUE}

Because it proves the eligibility of the table, each object in the query can be rendered at a different point in time. However, you are farthest from returning only the specified undo_retention parameter, or the most recent DDL command (Create/alter/drop), whichever is in front.

Suppose two employees are describing a "edit war" on a part of the parts table. Everyone thinks his or her changes are not saved by the database. In fact, everyone is changing the value "back" to where they think it is appropriate. You can learn what happened by extracting the version history of that line. List A shows the query and its results.

Several new artifacts are listed as you provide transaction information that affects the row. Versions_starttime and VERSIONS_STARTSCN let you know the first line of history. There is also a VERSIONS_XID column (not shown) that indicates the transaction ID, and you can apply it to study other rows--even other rows in other tables--for changes that occur at the same time.

Since there have been many updates, you can query the database to find the only rowid of the row. You can then use a related feature-backtracking transaction queries-to see which users have made changes and in what order they submit their data. List B shows the query and its results.

The note here is the row_id column, which is different from the ROWID pseudo column (see underlined section). It's just a simple column in the Flashback_transaction_query view.

Now you can tell these two users to stop modifying both sides of the work.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience and has been a technical trainer, consultant and database administrator. Visit Bob's Web site.