Working with Flashback Table in Oracle DBA

Ratings:
(4.3)
Views:1546
Banner-Img
  • Share this blog:

Flashback Table in Oracle DBA

The FLASHBACK TABLE the command allows point in time recovery of individual tables subject to the following requirements.

  • You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
  • You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
  • There must be enough information in the undo tablespace to complete the operation.
  • Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).

Syntax Of Flashback Table

flashback syntax

The following example creates a table, inserts some data, and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.

CREATE TABLE flashback_table_test (
  id  NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715315

INSERT INTO flashback_table_test (id) VALUES (1);
COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         0

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1



Flashback of tables can also be performed using timestamps.

FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');

 

Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore Oracle DBA Training

What is the Oracle Flashback Database?

Flashback Database is a part of the backup & recovery enhancements in Oracle 10g Database that are called Flashback Features. The main purpose of Flashback Technology is to let you quickly recover from logical corruptions or user errors. Oracle Flashback Features include the following:

  • Flashback DATABASE
  • Flashback DROP
  • Flashback TABLE
  • Flashback QUERY
  • Flashback VERSIONS QUERY
  • Flashback TRANSACTION QUERY
What is a flashback 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. With flashback features, you can do the following: Perform queries that return past data.

Recovering Tables Using Oracle Flashback Table

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle DBA Interview Questions

Oracle Flashback Table:
  • Restores all data in a specified table to a previous point in the time described by a timestamp or SCN.
  • Performs the restore operation online.
  • Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.
  • Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.
  • Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.
  • Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

Benefits Of Flashback

Flashback Database provides:

  • A very effective way to recover from complex human errors
  • Faster database point-in-time recovery
  • Simplified management and administration
  • Little performance overhead

For in-depth knowledge on Oracle DBA click on:

 

About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.