Before You Begin

This 10-minute tutorial shows you how to use Oracle Flashback Table to rewind tables.

Select the Oracle Database release:

Background

You can use Oracle Flashback Table to rewind the contents of one or more tables back to a previous point in time without affecting other database objects. This feature enables you to recover from logical data corruption such as the accidental addition or deletion of rows.

What Do You Need?

• Oracle Database 18c19c

Enable Row Movement on a Table

In this section you enable row movement on the HR.REGIONS table. Row movement must be enabled on a table before you can use Flashback Table.

1. Invoke SQL*Plus and connect as the SYSTEM user.
2. Alter session to pluggable database container orclpdb.

   alter session set container=orclpdb;
   
   Session altered.

3. Enable row movement on the HR.REGIONS table.

   SQL> alter table hr.regions enable row movement;
   
   Table altered.

Simulate User Error

In this section you simulate user error by changing data in the HR.REGIONS table.

1. Query the HR.REGIONS tables by executing the following command.

   SQL> select * from hr.regions;
   
    REGION_ID REGION_NAME
   ---------- -------------------------
            1 Europe
            2 Americas
            3 Asia
            4 Middle East and Africa

2. Simulate user error by executing the following SQL commands to change the value in the REGION_NAME column in all the rows.

   SQL> update hr.regions set region_name = 'ORACLE';
   
   4 rows updated.

   SQL> commit;
   
   Commit complete.

3. Once again, query the HR.REGIONS table.

   SQL> select * from hr.regions;
   
    REGION_ID REGION_NAME
   ---------- -------------------------
            1 ORACLE
            2 ORACLE
            3 ORACLE
            4 ORACLE

   Notice that the REGION_NAME for all the rows is updated to ORACLE.

Perform Flashback Table

In this section you rewind the HR.REGIONS table to a point prior to the update you performed to simulate user error.

1. Use the FLASHBACK TABLE command to flash back table to a time before you performed the update to the HR.REGIONS table.

   SQL>flashback table hr.regions to timestamp to_timestamp('2016-08-11 07:30:00', 'YYYY-MM-DD HH:MI:SS');
   
   Flashback complete.

2. Query the HR.REGIONS table to verify that the values in the REGION_NAME column have been restored.

   SQL> select * from hr.regions;
   
    REGION_ID REGION_NAME
   ---------- -------------------------
            1 Europe
            2 Americas
            3 Asia
   4 Middle East and Africa

3. Exit from SQL*Plus.