Working with Flashback Table in Oracle DBA

Flashback Table in Oracle DBA

The FLASHBACK TABLE 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');
Interested in mastering Oracle DBA Training? Enroll now for FREE demo on 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.

Check out the top Oracle DBA Interview Questions now!

Oracle Flashback Table:

  • Restores all data in a specified table to a previous point in 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:

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

For Indepth knowledge on Oracle DBA click on:

 

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Working with Flashback Table in Oracle DBA"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Support


    Please leave a message and we'll get back to you soon.

    3 + 5