Flashback TABLE
According to Donald Burleson “The Flashback Table allows you to recover tables to a specific point in time without restoring from a backup. With this feature, the data in the tables and all associated objects including indexes, constraints, triggers, etc are restored”.
Source (http://www.dba-oracle.com/t_flashback.htm)
Flashback Table operations are not valid for the following object types:
- Tables that are part of a cluster
— Materialized views
— Advanced Queuing tables
— Static data dictionary tables
— System tables
— Partitions of a table
— Remote tables (via database link)
Source ( https://community.oracle.com/thread/556608)
Limitations:
Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time.
However, if the following DDL commands are issued, the flashback table command does not work:
- ALTER TABLE … DROP COLUMN
— ALTER TABLE … DROP PARTITION
— CREATE CLUSTER
— TRUNCATE TABLE
— ALTER TABLE … MOVE
Source ( https://community.oracle.com/thread/556608)
Flashback Table Privileges:
You must have the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege to use the Flashback Table feature.
Example 1: Flashback Table using SCN:
SCN — System change number is a number that represents a point in time after a commit occurs (after a transaction ends), it marks a consistent point in time in the database. To find the current SCN number:
[SELECT CURRENT_SCN FROM V$DATABASE];
Now I will make a couple of update statements and then we’ll see how can we bring the data back to its original state? Using the query below, we can see how many changes have been made to the table:
So let’s say I want to see the data based on the 1st version_startscn number — which is the first data change ever made to ctnmst_bk_2019116 on Nov 17, 2019, 6:52:44 AM. So, we naturally have to assume that any changes before that would be the original version of the table. So, I can simply use the following query:
[select * tablename as of scn 255669186-1 where rownum < 3]
&
[select * from tablename as of scn 255669186 where rownum < 3]
;
This is just a simple way to show that since SCN 255669186 was the first recorded change, so any changes before that would be the original version of the table, so I added -1 to make the number to 255669185, as shown above.
In the image below, I am showing all of the changes ever made to this table, including partial changes I made only to the ctncod 01 as highlighted in the image below. I have also provided a script you can use to practice on your test instances.
publish data where versions_startscn = '255669185'
&
[select distinct to_char(versions_startscn) versions_startscn, versions_starttime, versions_endscn,
versions_endtime, versions_operation, count(versions_xid)
from CTNMST_BK_20191116
versions between scn minvalue and maxvalue
where versions_startscn is not null
group by versions_startscn, versions_starttime, versions_endscn,
versions_endtime, versions_operation
order by 1
]
|
[select ctncod, wh_id, ctnlen, ctnwid, ctnhgt, ctnwgt, ctnwlm, ctnfpc, last_ctn_fpc, mod_usr_id
from CTNMST_BK_20191116 as of scn @versions_startscn
where rownum < 3];Example 2: Flashback Table using TIMESTAMP:
In this example, you will be able to recover overwritten data by using timestamp instead of SCN. But please be aware that if a table’s data has been changed multiple times in a minute, then this may not be the best way to recover data as shown in the images below:
Syntax:[select * from CTNMST_BK_20191116 as of timestamp systimestamp — interval ‘1’ hour];[select * from CTNMST_BK_20191116 as of timestamp systimestamp — interval ‘25’ minute];
Example 3: Recover Overwritten Values:
What if the rows weren’t deleted, just updated? And you need to restore the original values, but don’t know what they are? You can use Flashback Query in an update statement to recover data to its previous state:
[update CTNMST_BK_20191116 newtable
set (ctncod, wh_id, ctnlen, ctnwid, ctnhgt, ctnwlm, ctnfpc, last_ctn_fpc, moddte, mod_usr_id, cube_rem_flg) =
(
select ctncod, wh_id, ctnlen, ctnwid, ctnhgt, ctnwlm, ctnfpc, last_ctn_fpc, moddte, mod_usr_id, cube_rem_flg
from CTNMST_BK_20191116 as of scn 255669185 oldtable
where newtable.ctncod = oldtable.ctncod
and newtable.wh_id = oldtable.wh_id
)
where 1=1
];
[select * from CTNMST_BK_20191116];
Example 4: Recovering deleted data via inserts
Let’s say I accidentally deleted a record or records. In this example, all records where mod_usr_id was SAHMAD.
Now, I will use the script below to recover the deleted data:
[delete from ctnmst_bk_20191116 where 1=1]
;
[INSERT into ctnmst_bk_20191116
SELECT *
FROM ctnmst_bk_20191116 as of scn 255669185
WHERE 1=1
] catch (-1403)
;
[select count(*) from ctnmst_bk_20191116]
Word of advice, if you want to avoid inserting duplicate records then you’ll need to change the WHERE 1=1 clause to match keys to avoid data duplication. If you do so, you will also not need the delete statement.
Converting SCN TO TIMESTAMP
You can also play around with converting from a TIMESTAMP to an SCN and an SCN to a TIMESTAMP.
[SELECT CURRENT_SCN FROM V$DATABASE]
|
[SELECT SCN_TO_TIMESTAMP(@current_scn) TIMESTAMP FROM DUAL];
Converting TIMESTAMP to SCN:
Likewise, you can also convert a specific timestamp to SCN:
[SELECT TIMESTAMP_TO_SCN(sysdate) SCN FROM DUAL];
Note: Flashback query doesn’t work for objects that were changed before a database was restarted. If you try to use flashback queries that attempt to select data before when the database was bounced, oracle database will generate the following error:
CONCLUSION
You can use either SCN or TIMESTAMP for flashback queries— it works both ways, it is just a matter of preference. It boils down to whether you’re comfortable working with numbers or time. Since various human factors come into play when discussing time like timezone, client-side time or system time) it may be safer to convert a time to an SCN.
