Flashback Technology in Oracle :
Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
Today we will see following feature of Flashback Technology :
1. Oracle Flashback Query .
2. Flashback Version Query.
3. Flashback Table.
4. Flashback Drop.
1. Oracle Flashback Query :
It is used to view committed data as it existed at some point in the past . Select command with AS OF clause references a time in the past through a time stamp or SCN.
SQL> SELECT * FROM CITY; CITY CITY_NM ENTERED_BY ENTERED_D LAST_ENTERED_BY LAST_MODI MACHINE_NM LAST_MACHINE_NM COMP BRAN STAT ---- ---------------------------------------------------------------------------------------------------- ---------------- --------- ---------------- --------- ------------------------- ------------------------- ---- ---- ---- AHD AHMEDABAD ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 PAL PALANPUR ADM 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 001 001 PUN PUNE ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 SB SECUNDARABAD ADM 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 001 001 THN THANE ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 UDA UDAIPUR ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 VAS VALSAD ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 YED YEDDUMAILARAM ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 8 rows selected. SQL>
I am updating 8 rows in the table
SQL> UPDATE CITY SET ENTERED_BY='SYS'; 8 rows updated. SQL> COMMIT; Commit complete.
Here we can see updated result.
SQL> SELECT * FROM CITY; CITY CITY_NM ENTERED_BY ENTERED_D LAST_ENTERED_BY LAST_MODI MACHINE_NM LAST_MACHINE_NM COMP BRAN STAT ---- ---------------------------------------------------------------------------------------------------- ---------------- --------- ---------------- --------- ------------------------- ------------------------- ---- ---- ---- AHD AHMEDABAD SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 PAL PALANPUR SYS 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 001 001 PUN PUNE SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 SB SECUNDARABAD SYS 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 001 001 THN THANE SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 UDA UDAIPUR SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 VAS VALSAD SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 YED YEDDUMAILARAM SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 8 rows selected. SQL>
Now lets check Flashback Query AS OF clause feature
SQL> SELECT * FROM city AS OF TIMESTAMP TO_TIMESTAMP('2017-12-19 12:40:00', 'YYYY-MM-DD HH:MI:SS');
CITY CITY_NM ENTERED_BY ENTERED_D LAST_ENTERED_BY LAST_MODI MACHINE_NM LAST_MACHINE_NM COMP BRAN STAT
---- ---------------------------------------------------------------------------------------------------- ---------------- --------- ---------------- --------- ------------------------- ------------------------- ---- ---- ----
AHD AHMEDABAD ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 128 001
PAL PALANPUR ADM 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 128 001
PUN PUNE ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 128 001
SB SECUNDARABAD ADM 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 128 001
THN THANE ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 128 001
UDA UDAIPUR ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 128 001
VAS VALSAD ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 128 001
YED YEDDUMAILARAM ADM 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 128 001
8 rows selected.
SQL>
We can see the difference in above query , updation has not affected at 12:40 am.
2. Flashback Version Query :
It is used to view committed historical data for a specific time interval. Use the VERSIONS BETWEEN clause of the SELECT command.
current status of table :
SQL> select * from city; CITY CITY_NM ENTERED_BY ENTERED_D LAST_ENTERED_BY LAST_MODI MACHINE_NM LAST_MACHINE_NM COMP BRAN STAT ---- ---------------------------------------------------------------------------------------------------- ---------------- --------- ---------------- --------- ------------------------- ------------------------- ---- ---- ---- AHD AHMEDABAD SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 010 100 PAL PALANPUR SYS 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 001 001 PUN PUNE SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 SB SECUNDARABAD SYS 13-SEP-17 ADM 13-SEP-17 SERVER SERVER 001 001 THN THANE SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 UDA UDAIPUR SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 VAS VALSAD SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 YED YEDDUMAILARAM SYS 13-SEP-17 sys 10-NOV-17 SERVER BRIJESH-PC 001 001 8 rows selected.
Note current timestamp or SCN of database.
SQL> select current_scn from v$database; CURRENT_SCN ----------- 239390439
Lets update some records.
SQL> update city set comp_cd='123' where city_cd='AHD'; 1 row updated. SQL> commit; Commit complete. SQL> update city set branch_cd='123' where city_cd='AHD'; 1 row updated. SQL> commit; Commit complete.
Check current SCN of database.
SQL> select current_Scn from v$database; CURRENT_SCN ----------- 239390482 SQL>
Apply version query between two SCN to see the result.
SQL> select city_cd,comp_cd,branch_cd from city versions between scn 239390439 and 239390482 where city_cd='AHD'; CITY COMP BRAN ---- ---- ---- AHD 123 123 AHD 123 100 AHD 010 100
3.Flashback Table :
It is used to rewind one or more tables to their contents at a previous time without affecting other database objects.
Create one table :
SQL> create table dbtest(no number,name varchar2(100),city varchar2(100)); Table created. Insert some records into it. SQL> insert into dbtest values(1,'james','newyork'); 1 row created. SQL> insert into dbtest values(2,'scott','london'); 1 row created. SQL> commit; Commit complete.
Note current SCN or timestamp.
SQL> select current_scn from v$database; CURRENT_SCN ----------- 239390860 SQL> select * from dbtest; NO NAME CITY --- ------- ---------------------------------------------------------------------------------------------------- ----------- 1 james newyork 2 scott london
Delete some records from the table.
SQL> delete from dbtest where no=1; 1 row deleted. SQL> commit; Commit complete.
check output, we can see record is deleted from the table
SQL> select * from dbtest; NO NAME CITY ---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 2 scott london SQL> flashback table dbtest to scn 239390860; flashback table dbtest to scn 239390860 * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled
Note : to use flashback table feature of oracle we must enable row movement on particular table.
SQL> alter table dbtest enable row movement; Table altered.
Now we can use flashback table feature.
SQL> flashback table dbtest to scn 239390860; Flashback complete.
see the output :
SQL> select * from dbtest; NO NAME CITY ---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 1 james newyork 2 scott london SQL>
We can also use timestamp syntax
flashback table dbtest to timestamp to_timestamp('19-12-2017 05:35:00','dd-mm-yyyy hh24:mi:ss');
4.Flashback Drop :
Reverses the effects of dropping a table by returning the dropped table from the recycle bin to the database along with dependent objects such as indexes and triggers.
SQL> select * from dbtest; NO NAME CITY ---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 1 james newyork 2 scott london SQL> SQL> drop table dbtest; Table dropped. SQL>
we can see table no longer exist
SQL> select * from dbtest; select * from dbtest * ERROR at line 1: ORA-00942: table or view does not exist
Now use flashback table to before drop option to reverse the effect of drop
SQL> flashback table dbtest to before drop; Flashback complete. Table is flash-backed to its original state of before drop. SQL> select * from dbtest; NO NAME CITY ---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 1 james newyork 2 scott london SQL>
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
