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>
