6.4. Using Flashback Versions Query

The Flashback Versions Query is an improvement to the Flashback Query technology first developed in Oracle 9i. Flashback Versions Query allows you to retrieve all of the versions of the rows that exist or existed between the times the query was executed to a determined point-in-time in the past. The Flashback Versions Query returns all the committed occurrences of the rows for a query of an object, while not displaying the uncommitted row versions.

Flashback Versions Query works by retrieving data from the UNDO tablespace. The UNDO_RETENTION initialization parameter, which is specified in seconds, determines how much committed undo data to keep in the database. If transactions need additional undo space and there is no more space in the UNDO tablespace, then Oracle will start reusing undo space. The RETENTION GUARANTEE tablespace and database option, which can be set on the UNDO tablespace, will protect unexpired undo data in this situation.

The default value for RETENTION in a tablespace is NO GUARANTEE. This value can be set during tablespace creation. This information can be viewed by querying the DBA_TABLESPACE view.


The privileges required to use the Flashback Versions Query are SELECT and FLASHBACK for each user. The Flashback Versions Query is performed with a new clause that can be added to a query statement: VERSIONS BETWEEN. The VERSIONS BETWEEN clause has two implementations. The first implementation is to specify the system change numbers (SCNs) to identify a start and stop point of the Flashback Versions Query. The second option uses a timestamp to identify a start and stop point of the Flashback Versions Query.

The VERSIONS BETWEEN clause can be used with SCN MINVALUE and MAXVALUE. Also, the VERSIONS BETWEEN clause can be used with TIMESTAMP TO_TIMESTAMP and TO_TIMESTAMP.

Let's look at a Flashback Versions Query in more detail:

  1. First, execute some update statements to generate changes from the Tl table for employee JONES:

    SQL> update tl set salary=18000 where employee='JONES';
SQL>commit;
SQL> update tl set salary=21000 where employee='JONES';
SQL>commit;
SQL> update tl set salary=25000 where employee='JONES';
SQL>commit;

  2. Next, query the table Tl with the VERSION BETWEEN option:

    SQL> select salary from tl
   2 versions between
   3 sen minvalue and maxvalue
   4 where employee = 'JONES';

    SALARY
----------
    25000
    21000
    18000
SQL>

The following example uses the VERSIONS BETWEEN TIMESTAMP to identify the changes to the SALARY column in the Tl table for employee JONES:

SQL> select salary from tl
   2 versions between timestamp

3to_timestamp('2004-10-26 11:37:01','YYYY-MM-DD HH:MI:SS') and
   4 to_timestamp('2004-10-26 11:43:01','YYYY-MM-DD HH:MI:SS')
   5 where employee = 'JONES';

    SALARY
----------
     25000
     21000
     18000
SQL>

The VERSIONS BETWEEN clause cannot produce versions of the rows past when certain DDL commands modify the table specifications. This means that if you perform ALTER TABLE and add a new column or delete a column, this may not work through that change.


There are some new database functions that help you work with the Flashback Versions Query. The SCN_TO_TIMESTAMP function can be used to find the timestamp of an SCN. The inverse of this function is called TIMESTAMP_TO_SCN, which will find an SCN based on a timestamp.

Let's use one of these new functions in a SQL statement:

SQL> select current_scn,scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

----------- -------------------------------
     725638 26-OCT-04 11.37.03.000000000 AM

There are several new pseudocolumns that help you work with the Flashback Versions Query:

  • VERSIONS_STARTTIME

  • VERSIONS_STARTSCN

  • VERSIONS_ENDTIME

  • VERSIONS_ENDSCN

  • VERSIONS_XID

  • VERSI0NS_0PERATI0N

These pseudocolumns are available for every Flashback Versions Query, and they can help identify when actual changes occur and how they were changed for specific queries.

Let's look at some of these pseudocolumns in an actual query:

SQL>
  1 select to_char(versions_starttime,'DD-MON HH:MI') "START DATE",
  2         to_char (versions_endtime, 'DD-MON HH:MI') "END DATE",
  3         versions_xid,
  4         versions_operation,
  5         employee,
  6         salary
  7         from test.t1
  8         versions between scn
  9         minvalue and maxvalue
  10*       where employee = 'JONES'

START DATE   END DATE     VERSIONS_XID     V EMPLOYEE SALARY
------------ ------------ ---------------- - -------- ----------
26-OCT 05:11              020018001F030000 U JONES    35000
26-OCT 05:06 26-OCT 05:11 0600270081000000 U JONES    31000
             26-OCT 05:06                    JONES    30000

As you can see, the pseudocolumns provide metadata about the versions of the row data in the T1 table. VERSIONS_XID is a unique identifier of the version metadata for that version of data. The V column is shortened for VERSIONS_OPERATION, which in this case displays a U for update, which shows how the data was changed. VERSIONS_STARTTIME and VERSIONS_ENDTIME is aliased to START DATE and END DATE, respectively. With the pseudocolumn information, you can identify when and how the data was changed. Table 6.1 describes each of these pseudocolumns.

Table 6.1. Flashback Pseudocolumns
PseudocolumnDescription
VERSIONS_STARTSCNStarting SCN when the row was first created. This identifies the SCN when the data first took on the values displayed in the row version. If NULL, the row version was created before the lower time bound of the query BETWEEN clause.
VERSIONS_STARTTIMEStarting TIMESTAMP when the row version was first created. This identifies the time when the data first took on the values displayed in the row version. If NULL, the row version was created before the lower time bound of the query BETWEEN clause.
VERSIONS_ENDSCNEnding SCN when the row version expired. This identifies the row expiration SCN. If NULL, then either the row version is still current or the row corresponds to a DELETE operation.
VERSIONS_ENDTIMEEnding TIMESTAMP when the row version expired. This identifies the row expiration time. If NULL, then either the row version is still current or the row corresponds to a DELETE operation.
VERSIONS_XIDIdentifier of the transaction that created the row version.
VERSIONS_OPERATIONThis is the operation performed by the transaction that modified the data. The values are I for insertion, D for deletion, or U for update.

As you can see, the Oracle 10g Flashback Versions Query is a significant improvement over the Flashback Query in Oracle 9i. The Flashback Versions Query can specify query versions by timestamp or SCN. The operations that performed these changes are also identified. The Flashback Versions Query, in conjunction with other Flashback Technologies, has many valuable uses to help fix logical corruptions in the database and many other user errors.

The Flashback Versions Query VERSIONS BETWEEN clause can be used in DDL and DML subqueries.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset