//
you're reading...
Administration, Flashback

Flashback Table

There are two Flashback feature related to database table: Flashback Table and Flashback Drop. We already discussed the Flashback Drop feature. In this post we will discuss the Flashback Table.

Flashback table feature allows you to flashback a table to a previous point-in-time that means you can discard all the changes that were made on the table since mention flashback time. Flashback Table depends on the availability of the UNDO data in the UNDO tablespace. That means flashing back to prior version of table depends on the availability of sufficient amount of UNDO data retained in your UNDO tablespace and the UNDO_RETENTION initializing parameter setting. The database should be in Automatic UNDO Management mode in order to FLASHBACK TABLE feature to work. When performing an Oracle Flashback Table operation, Oracle Database acquires exclusive DML locks on table/ tables specified in the Flashback list. These locks prevent any operations on the tables while they are reverting to their earlier state.

SYNTAX:
We will talk about the following SYNTAX of the FLASHBACK TABLE:

FLASHBACK TABLE [schema.] table [, [schema.] table]. . .
TO
{{SCN|TIMESTAMP} expr
[{ENABLE|DISABLE} TRIGGERS]};

NOTE:
Whereas the Flashback Drop feature allows you to recover a DROP table [not truncated], the Flashback Table feature allow you to recover a table into its prior state. The other difference between these two distinct flashback feature is Flashback Table is entirely depend on the UNDO data available in the UNDO Segments of the database whereas Flashback Drop uses Recycle Bin, not Undo data.

The full syntax for FLASHBACK TABLE is:

FLASHBACK TABLE [schema.] table [, [schema.] table]. . .
TO
{[{SCN|TIMESTAMP} expr |RESTORE POINT restore_point]
[{ENABLE|DISABLE} TRIGGERS]
|BEFORE DROP [RENAME TO table]};

FLASHBACK TABLE is one of the flashback techniques that use UNDO segments. The others are FLASHBACK QUERY, FLASHBACK VERSION QUERY, FLASHBACK TRANSACTION and FLASHBACK TRANSACTION BACKOUT.

In order to use FLASHBACK TABLE, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table.

There are two main clauses with the Flashback Table:
TO_SCN and TO_TIMESTAMP. TO_SCN flash back the table to a certain SCN [SYSTEM CHANGE NUMBER], whereas TO_TIMESTAMP restore a table to certain time period.

NOTE:
You can convert TIMESTAMP to a corresponding SCN and SCN to TIMESTAMP using SQL functions TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP respectively.

dbadmin@O11R2>SELECT current_scn, scn_to_timestamp(current_scn)
             FROM v$database;

CURRENT_SCN          SCN_TO_TIMESTAMP(CURRENT_SCN)
-----------          -----------------------------------------
    1267944          12-SEP-12 03.48.05.000000000 PM

dbadmin@O11R2>SELECT timestamp_to_scn(sysdate) FROM dual;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
                  1268087

Performing a Flashback Table Operation

In the following section we delete some rows from the student table and after that we restore table to the time frame as it has all the rows that were affected by the delete operation. But first note down the current SCN.

hr@O11R2>SELECT current_scn FROM v$database;
CURRENT_SCN
-----------
1281438

hr@O11R2>SELECT NAME, VALUE/60 MINUTES_RETAINED
         FROM   V$PARAMETER
         WHERE  NAME = 'undo_retention';
NAME                          MINUTE_ETAINED
--------------------          -----------------
undo_retention                               15

hr@O11R2>DELETE FROM student
         WHERE zip IN (07010, 07024);
15 rows deleted.

hr@O11R2>COMMIT;
Commit completed.

hr@O11R2>SELECT * FROM student
         WHERE zip IN (07010, 07024);
No rows selected.

hr@O11R2>FLASHBACK TABLE student TO SCN 1281438;
FLASHBACK TABLE STUDENT TO SCN 1281438;
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

The operation is failed with ORA-08189 Error because of default row movement. Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table using TO BEFORE DROP since FLASHBACK DROP uses dropped data in the recycle bin rather than undo data. The flashback operation doesn’t preserve the ROW-IDs when it restores the rows. There is no guarantee that the ROW-IDs will correspond to the same rows after the flashback.

NOTE:
Also check the Prerequisites of Flashback Table. Make sure the table doesn’t belong to system tablespace.

hr@O11R2>ALTER TABLE student ENABLE ROW MOVEMENT;
Table altered.

hr@O11R2>FLASHBACK TABLE student TO SCN 1281438;
Flashback completed.

hr@O11R2>SELECT student_id, salutation, first_name, last_name, street_address, zip, phone
         FROM student
         WHERE zip IN (07010, 07024);

STUDENT_ID SALUT FIRST_NAME   LAST_NAME            STREET_ADDRESS                ZIP     PHONE
---------- ----- ------------ ---------------      ---------------------------   -----   ---------------
       323 Mr.   Gilbert      Ginestra             555 North Ave, 24C            07024   201-555-5555
       341 Mr.   Kevin        Porch                1531 John St.                 07024   201-555-5555
       217 Mr.   Jeffrey      Citron               PO Box 1091                   07024   201-555-5555
       229 Ms.   Adrienne     Lopez                755 Anderson Ave. #3-25       07010   201-555-5555
       369 Ms.   Lorraine     Tucker               200 Winston Dr.               07010   201-555-5555
       370 Mr.   John         Mithane              770 Amsterdam Ave. #20K       07010   201-555-5555
       158 Mr.   Roy          Limate               5 Horizon Rd.                 07024   201-555-5555
       182 Mr.   Jeffrey      Delbrun              PO Box 1091                   07024   201-555-5555
       105 Mr.   Angel        Moskowitz            320 John St.                  07024   201-555-5555
       233 Ms.   Kathleen     Mulroy               770 Anderson Ave.             07010   201-555-5555
       256 Ms.   Lorrane      Velasco              200 Winston Dr. #2212         07010   201-555-5555
       262 Ms.   Donna        Walston              236 Washington Ave.           07024   201-555-5555
       267 Mr.   Julius       Kwong                3001 Edwin Ave. 2B            07024   201-555-5555
       273 Ms.   Hedy         Naso                 1072 Abbott Blvd.             07024   201-555-5555
       286 Ms.   Robin        Kelly                200 Winston Dr. #2212         07010   201-555-5555

15 rows selected.

You can flashback more than one table at a time provided that both have row movement enable.

FLASHBACK TABLE student, instructor TO SCN 1282009;

You cannot restore a table to an earlier state across any DDL operations involving a change in the table structure. The following DDL operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

hr@O11R2>UPDATE student
         SET zip='A7609'
         WHERE zip=07010;
6 rows updated.

hr@O11R2>DELETE FROM student
         WHERE TO_CHAR(registration_date,'DD-MM-YYYY')='13-02-2003';

29 rows deleted.

hr@O11R2>COMMIT;
Commit complete.

hr@O11R2>ALTER TABLE student DROP COLUMN ex;
Table altered.

hr@O11R2>FLASHBACK TABLE student TO TIMESTAMP
         TO_TIMESTAMP ('2012-09-12 21:27:00', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE student TO TIMESTAMP
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

NOTE:
One of the advantages of the FLASHBACK TABLE is you don’t have to take tablespaces or database offline because it relies on the UNDO data. It simply deletes the new rows and reinserts the old rows.


You cannot rollback a FLASHBACK TABLE statement but you can issue another FLASHBACK TABLE statement and specify a time prior to the previous FLASHBACK TABLE statement.


By default Oracle disables all the triggers and re-enabled them after the FLASHBACK TABLE statement has been completed. However you can change this behavior using ENABLE TRIGGERS in the FLASHBACK TABLE statement.

FLASHBACK TABLE STUDENT
TO SCN 1281438
ENABLE TRIGGERS;


The entire FLASHBACK TABLE statement executes as a single transaction regardless of the number of tables specified the flashback list, it succeed entirely or it fails. In the case of multiple tables all the table must be restore to the earlier state or none of them do.


DOCUMENTATION:

Rewinding a Table with Flashback Table

SQL LANGUAGE REFERENCE: FLASHBACK TABLE

Happy Learning 🙂

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: