flashback query in oracle 9i

This short article introduces flashback query, a new feature of Oracle 9i. Flashback query enables us to query our data as it existed in a previous state. In other words, we can query our data from a point in time before we or any other users made permanent changes to it.

Flashback query works in tandem with another new feature of Oracle 9i; automatic undo management, whereby Oracle manages our rollback segments for us. In fact, the term "rollback segments" is confined to history (or applies only to manual undo management at least). From 9i, we know these segments as "undo segments" and under automatic management, Oracle will create as many or as few as are required to satisfy our transaction workloads. This is, of course, within system limits; these being the size of the undo tablespace and a new parameter named undo_retention. This parameter specifies the number of seconds that Oracle should retain undo data for us.

Flashback query enables us to query the undo segments directly, either by SCN (System Change Number) or by timestamp. The means and ease of doing this changed dramatically between Oracle 9.0 and 9.2 and we shall examine both of them in this article. It is unlikely, however, that any users of 9i Release 2 (9.2) will wish to use the 9.0 method and we shall see why below.

requirements

To be able to use flashback query, we require the following system elements:

sample data

For our sample data we will create a test table and copy a few rows and columns of ALL_TABLES as follows.

SQL> CREATE TABLE t
  2  AS
  3     SELECT owner, table_name, tablespace_name
  4     FROM   all_tables
  5     WHERE  ROWNUM <= 5;

Table created.

SQL> SELECT * FROM t;

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             SEG$            SYSTEM
SYS             CLU$            SYSTEM
SYS             OBJ$            SYSTEM
SYS             FILE$           SYSTEM
SYS             COL$            SYSTEM

5 rows selected.

A small note on new tables, which applies to both releases of 9i, is that it might not be possible to begin flashback queries against them immediately. Oracle recommends that we wait for approximately five minutes, which equates to the intervals at which the SCN is mapped to a timestamp. The SCN itself is incremented with every commit. Attempting to flashback query a new table before then is likely to result in ORA-01466: unable to read data - table definition has changed.

9i release 1: dbms_flashback

Flashback queries in Oracle 9.0 use calls to DBMS_FLASHBACK to enable and disable the feature either side of a SQL statement or the opening of a PL/SQL cursor. We'll move straight onto an example to show how this works. In the following example, we will update our data and commit the changes. We will then enable flashback query to a point in time before this change and then run a query against the sample table. Finally we will disable flashback query to enable us to resume "normal" query mode.

Note that when we enable flashback query, we provide either a timestamp or SCN. We will use the SCN for the 9.0 examples and timestamps (another new feature of 9i) for the 9.2 examples later on. Because a timestamp is mapped to an SCN number every five minutes, the SCN offers a much finer level of precision for flashback. We will begin, therefore, by capturing the current SCN and permanently updating our sample data.

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS scn
  2  FROM   dual;

       SCN
----------
    592967

1 row selected.

SQL> UPDATE t SET tablespace_name = LOWER(tablespace_name);

5 rows updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t;

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             SEG$            system
SYS             CLU$            system
SYS             OBJ$            system
SYS             FILE$           system
SYS             COL$            system

5 rows selected.

Now we can enable flashback query to the point in time before we changed our data (i.e. using the earlier SCN). Once in "flashback query mode", any queries we run will return data consistent with the SCN we enabled. We can see this below.

SQL> exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(592967);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM t;

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             SEG$            SYSTEM
SYS             CLU$            SYSTEM
SYS             OBJ$            SYSTEM
SYS             FILE$           SYSTEM
SYS             COL$            SYSTEM

5 rows selected.

We can now see our data in its original state (i.e. before we changed the TABLESPACE_NAME to lower-case). To leave "flashback query mode", we must disable it using DBMS_FLASHBACK as follows. We can now see the data in its current, post-update form.

SQL> exec DBMS_FLASHBACK.DISABLE;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM t;

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             SEG$            system
SYS             CLU$            system
SYS             OBJ$            system
SYS             FILE$           system
SYS             COL$            system

5 rows selected.

There are a number of uses for this feature, such as data recovery, adhoc change-tracking or point-in-time queries, but of course, this is restricted to the period specified by undo_retention (with some variance depending on transaction loads). In fact, Oracle suggests that it is feasible to build flashback query capabilities into our applications. We might view this as a somewhat ambitious claim for the technology, but as a short-term recovery mechanism, it is very useful.

9i release 2: as of [scn|timestamp]

As we saw in the previous section, flashback query in 9i Release 1 is somewhat involved. Oracle 9i Release 2 makes flashback query significantly easier by building the capability into the SQL FROM clause itself. In 9.2, we can query our data AS OF TIMESTAMP or AS OF SCN directly. Flashback query does not need to be explicitly enabled and disabled via package calls; it is invoked directly by this syntax.

We will repeat the earlier example but this time using 9.2 flashback query. Furthermore, we'll use a timestamp (another new feature of Oracle 9i) instead of an SCN. Note that the initial ALTER SESSION is simply a convenience to enable a consistent timestamp format mask without having to supply it explicitly.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> SELECT LOCALTIMESTAMP
  2  FROM   dual;

LOCALTIMESTAMP
------------------------
22-NOV-2002 21:31:01.750

1 row selected.

SQL> UPDATE t SET table_name = LOWER(table_name);

5 rows updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t;

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             seg$            system
SYS             clu$            system
SYS             obj$            system
SYS             file$           system
SYS             col$            system

5 rows selected.

Now we can invoke flashback query as of the timestamp prior to our update.

SQL> SELECT *
  2  FROM   t AS OF TIMESTAMP TO_TIMESTAMP('22-NOV-2002 21:31:01.750');

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             SEG$            SYSTEM
SYS             CLU$            SYSTEM
SYS             OBJ$            SYSTEM
SYS             FILE$           SYSTEM
SYS             COL$            SYSTEM

5 rows selected.

This is much easier! The extended FROM clause is simple and intuitive to use and is more likely to encourage developers to use flashback query. A particularly good use for this is for resetting test data during development and unit-testing. It is also a good short-term recovery tool for rectifying minor mistakes.

a note on flashback precision

As noted earlier, flashback query timestamps are mapped to SCNs, but only once every five minutes. This makes flashback queries with timestamps subject to precision errors. We can see the effect of this in our 9.2 flashback query results above. Our flashback query correctly returns the original upper-case TABLE_NAME data but it also returns the original upper-case TABLESPACE_NAME data also. This tells us that the SCN that Oracle mapped to our timestamp is from a time before we ran the 9.0 example.

Using an SCN, however, we can be more precise with our flashback query. We will take a guess that the SCN after our 9.0 example update will be a few greater than before we began (there is no other activity on this test system). Using this, we will try a 9.2 flashback query AS OF SCN.

SQL> SELECT * FROM t AS OF SCN 592969;

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             SEG$            system
SYS             CLU$            system
SYS             OBJ$            system
SYS             FILE$           system
SYS             COL$            system

5 rows selected.

We can now see the data as it existed after the first update (9.0 example) but before the second update (9.2). Using the SCN enabled us to be far more precise with our flashback query.

recovering data

Finally for this article, the 9i Release 2 flashback query syntax makes it much easier to recover data. Using the AS OF syntax, we can either update the table from the flashback query source or we can delete the current data and insert the flashback data. In the following example, we'll remove the current data and replace it with the data as it existed after our 9.0 examples (i.e. lower-case TABLESPACE_NAME).

SQL> DELETE FROM t;

5 rows deleted.

SQL> INSERT INTO t
  2  SELECT * FROM t AS OF SCN 592969;

5 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t;

OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
SYS             SEG$            system
SYS             CLU$            system
SYS             OBJ$            system
SYS             FILE$           system
SYS             COL$            system

5 rows selected.

Data changes between current and flashback data can be identified quite simply by set queries using MINUS/UNION. If we need to search for changes to specific columns, we can join the current and flashback datasets together (we can also use the 9i FULL OUTER JOIN for this purpose). In the 9i Release 1 variant of flashback query, this would only be possible in PL/SQL. The sequence would be: enable flashback -> open flashback cursor -> disable flashback -> open current cursor -> fetch and compare data. As stated throughout this article, this simplicity of flashback query in 9i Release 2 makes this complex variant largely redundant.

further reading

For an overview of flashback query in 9i Release 1, including its limitations, see the 9.0 Application Developers Guide. The DBMS_FLASHBACK package can be found in the Package and Types Reference. The 9i Release 2 flashback query overview can be found in the 9.2 Application Developers Guide.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, November 2002

Back to Top