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.
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.
Daily Tech Insider Newsletter
Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.
Delivered Weekdays
