In the past, Oracle always decided the point in time at which our queries would be consistent. That is, Oracle made it such that any resultset we opened would be current with respect to one of two points in time:
•\ The point in time the query was opened: This is the default behavior in READ COMMITTED isolation (we’ll be covering the differences between READ COMMITTED, READ ONLY, and SERIALIZABLE transaction levels in Chapter 7).
•\ The point in time the transaction that the query is part of began: This is the default behavior in READ ONLY and SERIALIZABLE transaction levels.
However, with Oracle’s flashback query feature, we can tell Oracle to execute a query “as of” (with certain reasonable limitations on the length of time you can go back into the past, of course). With this, you can “see” read consistency and multiversioning even more directly.
Note The flashback data archive, used for long-term flashback queries (months or years into the past), does not use read consistency and multiversioning to produce the version of data that was in the database at some prior point in time. Instead, it uses before image copies of the records it has placed into the archive. We’ll come back to the flashback data archive in a later chapter. Note also that the flashback data archive is a feature of the database available for use without additional license cost.
Consider the following example. We start by getting an SCN (System Change or System Commit Number; the terms are interchangeable). This SCN is Oracle’s internal clock: every time a commit occurs, this clock ticks upward (increments). We could use a date or timestamp as well, but here the SCN is readily available and very precise:
$ sqlplus scott/tiger@PDB1
SQL> variable scn number
Note The DBMS_FLASHBACK package might have restricted access on your system. You may have to grant execute on this package to the schema you’re using before you can access it.
We retrieved the SCN so we can tell Oracle the point in time we’d like to query “as of”; we could also use a date or timestamp in place of an SCN. We want to be able to query Oracle later and see what was in this table at this precise moment in time. First, let’s see what is in the EMP table right now:
SQL> select count(*) from emp;
COUNT(*)
However, using the flashback query, with either the AS OF SCN or AS OF TIMESTAMP clause, we can ask Oracle to reveal to us what was in the table as of that point in time:
SQL> select count(*), :scn then_scn, dbms_flashback.get_system_change_number now_scn from emp as of scn :scn;
COUNT(*) THEN_SCN NOW_SCN
With Oracle, you have a command called “flashback” that uses this underlying multiversioning technology to allow you to return objects to the state they were at some prior point in time. In this case, we can put EMP back the way it was before we deleted all of the information (as part of doing this, we’ll need to enable row movement, which allows the rowid assigned to the row to change—a necessary prerequisite for flashing back a table):
SQL> alter table emp enable row movement; Table altered.
SQL> flashback table emp to scn :scn;
Flashback complete.
This is what read consistency and multiversioning are all about. If you don’t understand how Oracle’s multiversioning scheme works and what it implies, you won’t be able to take full advantage of Oracle or write correct applications in Oracle (ones that will ensure data integrity).
Note A flashback table requires the Enterprise Edition of Oracle.
