Data Management

Trace the evolution of rows with Oracle 10g's Flashback Versions Query

One of the trickiest problems to troubleshoot is the interaction between multiple applications that access the same data. See code samples that demonstrate how Oracle 10g's Flashback Versions Query feature makes this task much easier.

One of the trickiest problems to troubleshoot is the interaction between multiple applications that access the same data. Although each application in itself may be well-behaved, each app may make assumptions about the data that another doesn't. As a result, rows can appear, get changed, and disappear when you least expect it.

In the past, the methodology for troubleshooting such issues was to dump the data while both programs were running to try to trace what happened when. The introduction of Log Miner made the task easier, but it was cumbersome to use. Now, in Oracle 10g, there is a new tool that does a similar job as Log Miner, but much more conveniently.

It's called Flashback Versions Query, and it relies on the Automatic Undo Management feature and the Undo tablespace to provide images of rows through time. Placed after the "FROM tablename" clause and before the table alias, the Flashback Versions Query syntax qualifies the tablename by indicating which versions of rows are to be included in the SELECT. The syntax is:

VERSIONS BETWEEN { SCN | TIMESTAMP}
   {exp | MINVALUE} AND {exp | MAXVALUE}

Because it qualifies the table, each object in the query can be represented at a different point in time. However, you can only go back as far as the UNDO_RETENTION parameter specifies, or the most recent DDL command (CREATE / ALTER / DROP), whichever comes first.

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!

Suppose that two employees are having an "edit war" on the description of a part in the PARTS table. Each thinks his or her change is not being preserved by the database, when in reality, each is changing the value "back" to what they think it should be. You can investigate what's happening by pulling up the history of versions for that row. Listing A shows your query and the results.

Several new pseudocolumns give you information about the transactions that affected the row. VERSIONS_STARTTIME and VERSIONS_STARTSCN tell you about the first row in the history. There is also a VERSIONS_XID column (not shown) that indicates the transaction ID; you can use it to research what other rows, even in other tables, were changed at the same time.

Seeing the multiple updates, you query the database to find the unique ROWID of the row. You can then use a related feature, Flashback Transaction Query, to pin down which users made the changes and in what order they committed the data. Listing B shows this query and its output.

One thing to note here is the ROW_ID column, which is not the same as the ROWID pseudocolumn (see the underscore). It's simply a column in the FLASHBACK_TRANSACTION_QUERY view.

Now you can go tell the two users to stop changing each others' work!

Miss a tip?

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

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

1 comments

Editor's Picks