Flashback Table in Oracle Database

Hi,

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

 

 

Read previous article before this.

 

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;

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.