How to Flashback a Table

Background & Overview

This real life example looks at recovering a table using flashback.

Example

oracle@MyHost1:MYDB0001> sqlplus MyUser

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 10 09:18:20 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Tue Nov 10 2015 09:18:00 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table MyTestTable(col1 char(10));

Table created.

SQL> --
SQL> -- Row movement must be enabled for flashback table to work
SQL> --
SQL> alter table MyTestTable enable row movement;

Table altered.

SQL> --
SQL> -- Lets check our current SCN.  This will be need later.
SQL> --
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    6306881

SQL> --
SQL> -- Lets see what data we have in the table
SQL> --
SQL> select * from MyTestTable;

no rows selected

SQL> --
SQL> -- Okay, so lets insert some data
SQL> --
SQL> insert into MyTestTable(col1) values('MyData');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> --
SQL> -- Get the new SCN after our commit
SQL> --
SQL>  select current_scn from v$database;

CURRENT_SCN
-----------
    6306923

SQL> --
SQL> -- Lets add a bit more data
SQL> --
SQL> insert into MyTestTable(col1) values('MoreData');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> --
SQL> -- Check the SCN again.
SQL> --
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    6306934

SQL> --
SQL> -- So what data do we have in our table?
SQL> --
SQL> select * from MyTestTable;

COL1
----------
MyData
MyData
MyData
MyData
MyData
MyData
MoreData
MoreData
MoreData
MoreData
MoreData

COL1
----------
MoreData

12 rows selected.

SQL> --
SQL> -- Now lets flashback the table to an SCN.  We will use the SCN we were at prior to our second data load.
SQL> --
SQL> flashback table MyTestTable to scn 6306923;

Flashback complete.

SQL> --
SQL> -- Check to see what data we have.
SQL> --
SQL> select * from MyTestTable;

COL1
----------
MyData
MyData
MyData
MyData
MyData
MyData

6 rows selected.

SQL> --
SQL> -- So that worked.  Now flashback again to the first SCN before adding any data.
SQL> --
SQL> flashback table MyTestTable to scn 6306881;

Flashback complete.

SQL> --
SQL> -- What data do we have?
SQL> --
SQL> select * from MyTestTable;

no rows selected

SQL> --
SQL> -- Looking good.  Now can we flashback the flashback?  Lets see if we can get all our data back using the last SCN?
SQL> --
SQL> flashback table MyTestTable to scn 6306934;

Flashback complete.

SQL> --
SQL> -- Check the data
SQL> --
SQL> select * from MyTestTable;

COL1
----------
MyData
MyData
MyData
MyData
MyData
MyData
MoreData
MoreData
MoreData
MoreData
MoreData

COL1
----------
MoreData

12 rows selected.

SQL> --
SQL> -- Hey presto!!  Back to where we started.  Don't forget to tidy up.
SQL> --
SQL> drop table MyTestTable;

Table dropped.

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
oracle@MyHost1:MYDB0001>
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License