Flashback Table in Oracle Database

Hi,

I will explain what is the Flashback database in Oracle in this article.

 

 

Read previous article before this.

Oracle Flashback Architecture

 

Flashback table feature is used to return a table to the Past. I use 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.

 

Create Table and insert test data.

SQL> create table MEHMET.SALIH (lastname varchar2(30));

Table created.


SQL> insert into MEHMET.SALIH values ('DEVECI');

1 row created.

SQL> commit;

Commit complete.

 

 

 

Enable row movement to perform Flashback table.

SQL> alter table MEHMET.SALIH enable row movement;

Table altered.

 

 

Query related table and see test data.

SQL> select * from MEHMET.SALIH;

LASTNAME
------------------------------
DEVECI

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, or this action can be done by Developers or other accidentally 😉

SQL> delete from mehmet.salih;

1 row deleted.

SQL> commit;

Commit complete.

 

Query table, result will be no rows select.

SQL> select * from MEHMET.SALIH;

no rows selected

SQL>

 

We can return this table to the past via Flashback table like following.

SQL>
SQL> flashback table MEHMET.SALIH to scn 11911147;

Flashback complete.

SQL>

 

 

 

 

Query related table again, you can see old data again.

SQL>
SQL> select * from MEHMET.SALIH;

LASTNAME
------------------------------
DEVECI

SQL>

We can’t use flashback table feauture 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;

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/