Database Reference
In-Depth Information
The results show us, from most to least recent, the history of our employee's salary
changes. He started off at a salary of $3,900, his 3 percent raise boosted him to $4,017,
and his 5 percent raise boosted him to $4,217.85. Finally, our hapless employee's salary was
cut in half, to $2,018.93 (presumably after he was unable to quickly restore the rows his
boss deleted since he didn't know about Flashback technologies).
You will also notice that the sample query used the clause
between scn
minvalue and maxvalue
to identify the range of versions to select. This
construct allows the user to quickly select all versions that are available in
the undo tablespace. This is a much cleaner solution than using artificially
low and high date ranges such as
BETWEEN TIMESTAMP TO_TIMESTAMP('01-
JAN-1700', 'DD-MON-YYYY')
AND
TO_TIMESTAMP('31-DEC-2999', 'DD-MON-YYYY')
.
As mentioned earlier, there are several pseudocolumns available in conjunction with
Flashback Version Query that can be used to identify when and how the changes were
originally made. These columns are identified in Table 8.3.
TABle 8.3
Flashback Version Query Pseudocolumns
Column Name
Description
VERSIONS_STARTTIME
The timestamp of the first version of the rows returned from
the query.
VERSIONS_ENDTIME
The timestamp of the last version of the rows returned from
the query.
VERSIONS_STARTSCN
The SCN of the first version of the rows returned from the query.
VERSIONS_ENDSCN
The SCN of the last version of the rows returned from the query.
VERSIONS_XID
The unique transaction ID under which the data was originally
changed. In Oracle 12
c
and 11
g
this is a raw value, whereas in
10
g
it was a character value.
VERSIONS_OPERATION
The type of operation that caused the change. Valid values are
as follows:
I
- Insert
U
- Update
D
- Delete
Search WWH ::
Custom Search