Flashback table feature is used to return a table to the Past. This feature as a DBA so often, because developers or business IT staffs are able to drop some tables by mistake, so if flashback is on, we can return this table to the past with flashback table feature.
In order to use this feature, we need to enable row movement. Execute following command to enable it.
SQL> Alter table TABLE_NAME enable row movement;
Let’s go to make an example about Flashback table to learn this feature very well. I will create sample table and insert Test data and delete it, then I will try to return it from Flashback.
SQL> create table ABC.SALIH (lastname varchar2(30));
Table created.
SQL> insert into ABC.SALIH values (‘RAHUL’);
1 row created.
SQL> commit;
Commit complete.
Enable row movement to perform Flashback table.
SQL> alter table ABC.SALIH enable row movement;
Table altered.
Query related table and see test data:-
SQL> select * from ABC.SALIH;
LASTNAME
——————————
RAHUL
SQL>
Now query current_scn and keep it. We will return table to the this SCN.
SQL> Select current_scn from v$database;
CURRENT_SCN
———–
11911147
SQL>
Now delete related table.
SQL> delete from ABC.salih;
1 row deleted.
SQL> commit;
Commit complete.
Query table, result will be no rows select:-
SQL> select * from ABC.SALIH;
no rows selected
SQL>
We can return this table to the past via Flashback table like following:-
SQL>
SQL>flashback table ABC.SALIH to scn 11911147;
Flashback complete.
SQL>
Query related table again, you can see old data again:-
SQL>
SQL> select * from ABC.SALIH;
LASTNAME
——————————
RAHUL
SQL>
We can’t use flashback table feature for each table. For example, it is not supported to use flashback features for system table, data dictionary table and remote table.
You can disable some tablespace’s flashback feature like following:-
Alter tablespace TABLESPACE_NAME flashback off;
***************************************************************************************
Our Website : https://www.ksplsoft.com/
About Us:
KSPL is a leading group of highly skilled professional group, who are having more than 10 years of experience in various technologies. We are providing support and maintenance for existing product like RDBMS, Reporting, Cloud Solutions, Mobile development and Web development etc. Also we develop new product using various technologies like cloud, Unix, Database, Reports, Mobile applications and web designing and creation. With a team of dedicated and creative engineers, we focus on mentioned work/projects you to meet the challenges with tight deadline and trust to market.
KSPLsoft 