Database Reference
In-Depth Information
We got 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:
SCOTT@ORA12CR1> select count(*) from emp;
COUNT(*)
----------
14
Now let's delete all of this information and verify that it's “gone”:
SCOTT@ORA12CR1> delete from emp;
14 rows deleted.
SCOTT@ORA12CR1> select count(*) from emp;
COUNT(*)
----------
0
SCOTT@ORA12CR1> commit;
Commit complete.
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:
SCOTT@ORA12CR1> select count(*),
2 :scn then_scn,
3 dbms_flashback.get_system_change_number now_scn
4 from emp as of scn :scn;
COUNT(*) THEN_SCN NOW_SCN
---------- ---------- ----------
14 13646156 13646157
Finally, if you are using Oracle10
g
and above, 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):
SCOTT@ORA12CR1> alter table emp enable row movement;
Table altered.
SCOTT@ORA12CR1> flashback table emp to scn :scn;
Flashback complete.
