Flashback Table in Oracle DBA
FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements.
- You must have either the
FLASHBACK ANY TABLEsystem privilege or have
FLASHBACKobject 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
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?
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 TABLEstatement and another table that is not included in the
- 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:
- Oracle Service BUS
- Working with Flashback Table in Oracle DBA
- Types of privileges of ‘SYSOPER’ in Oracle DBA