Flashback Query

Oracle Requirements:

• SELECT ANY TABLE privilege.

• Sufficient UNDO tablespace.

Licensing:

• Available in all Oracle Database editions.

Flashback Query in Oracle

The Flashback Query feature in Oracle allows you to query historical data at a specific point in time. Basically you can see how the table was in a specific period of the past. This feature is useful for recovering accidentally deleted or modified data, performing audits, and analyzing how data has changed over time.

How Does Flashback Query Work?

Flashback Query leverages the UNDO tablespace to access past states of the database. When you perform a DML operation (like UPDATE, DELETE, or INSERT), Oracle keeps the original data in the undo segment. Flashback Query uses this undo data to reconstruct the table's state at the specified time or System Change Number (SCN).

Enabling Flashback Query

Flashback Query relies on undo data, so ensuring that you have sufficient undo tablespace is critical.

Verify Undo Tablespace Configuration

SQL> SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, guarantee FROM dba_data_files WHERE tablespace_name LIKE 'UNDOTBS%';
        

Check and extend the Undo Retention Period - In seconds

SQL> SHO PARAMETER UNDO_RETENTION;
SQL> ALTER DATABASE SET UNDO_RETENTION = 900;
        

I also recommend to configure the UNDO tablespace to be guarantee, to avoid data from being purged due to lack of free space

SQL> SHO PARAMETER undo_tablespace
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
        

Syntax and Examples

Querying Data Using a Specific Timestamp

SQL> SELECT * FROM MY_TABLE AS OF TIMESTAMP 
	TO_TIMESTAMP('2024-12-20 09:30:00', 'YYYY-MM-DD HH:MI:SS');
        

Creating a Table from the Flashback Query

SQL> CREATE TABLE NEW_TABLE AS SELECT * FROM MY_TABLE AS OF TIMESTAMP 
	TO_TIMESTAMP('2024-12-20 09:30:00', 'YYYY-MM-DD HH:MI:SS');
        

Restoring Specific Informations Using the Flashback Query

INSERT INTO MY_TABLE 
    (SELECT * FROM MY_TABLE AS OF TIMESTAMP 
     TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE name = 'JOHN');
        

You can do the same using SCN

SQL> SELECT * FROM MY_TABLE AS OF SCN 123456;
        

Limitations and Considerations

• Undo Availability – Flashback Query depends on the availability of undo data. If undo data is overwritten, flashback queries may fail.
• Performance Impact – Running queries against large datasets with undo can impact performance.
• Retention Policies – Ensure the undo retention period is long enough to support your recovery and audit requirements.

Flashback Query is a powerful feature that minimizes downtime and data loss by enabling rapid data recovery and auditing. By configuring undo tablespace properly and understanding the feature's limitations, you can take full advantage of Flashback Query in your Oracle databases.