• Arun Kumar

Flashback Table to SCN

In this article we will look at table flashback to a particular SCN number. Let’s take a scenario where you delete one record from a table and want to flashback table to SCN before record delete.


Check Current SCN

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;


Delete One Record

SQL> conn scott/tiger
SQL> SELECT * FROM EMP;
SQL> DELETE FROM EMP WHERE EMPNO=7934;
SQL> commit;


Flashback Table to SCN


To flashback table to SCN before record delete, we must enable row movement on the table as sys user

SQL> conn / as sysdba
SQL> SELECT table_name, row_movement FROM dba_tables where table_name='EMP';
SQL> Alter table scott.emp enable row movement;

Flashback the table to before drop SCN number.

SQL> FLASHBACK TABLE EMP TO SCN <scn_no>;
SQL> SELECT * FROM SCOTT.EMP;



Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback