Database Reference
In-Depth Information
Figure 13.9
Flashback AS OF
and
VERSIONS
Query Syntax.
13.3.2
Versions Query Pseudocolumns
Several versions query pseudocolumns allow retrieval of identifying infor-
mation about different versions of the same row in a flashback query:
ORA_ROWSCN
. Returns a row SCN.
VERSIONS_{START|END}TIME
. First and last version timestamp.
VERSIONS_{START|END}SCN
. First and last version SCN.
VERSIONS_XID
. Transaction identifier.
VERSIONS_OPERATION
. Returns (I)nsert, (U)pdate, or (D)elete.
Now let's look at some simple examples of flashback queries.
13.3.3
Using Flashback Queries
First I add a new row to the CONTINENT table.
INSERT INTO CONTINENT VALUES(CONTINENT_ID_SEQ.NEXTVAL
, 'South East Asia');
Now I use an AS OF flashback query to look at all continents before
inserting South East Asia. Figure 13.10 shows that the row did not exist
yesterday.
SELECT * FROM CONTINENT
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);
