Oracle DBMS_FLASHBACK Enable At Time: Set Your Session Back in Time

calendar Mar 12, 2026 · 5 min read · Oracle Flashback DBMS_FLASHBACK Undo Management PL/SQL DBA Scripts Point-in-Time Recovery Database Administration  ·
Share on: linkedin copy

Oracle DBMS_FLASHBACK Enable At Time: Session Flashback Guide

Oracle's DBMS_FLASHBACK.ENABLE_AT_TIME is a powerful procedure that lets you travel back in time within your database session — letting you query data exactly as it existed at a specific past moment, without needing a full database restore.

What This Code Does

This one-line call puts your entire Oracle session into "flashback mode," pointed at a specific past date and time. All SELECT queries you run after this call will return data as it was at that moment — not the current data. This is ideal for investigating accidental deletes, data corruption, or auditing historical states.

The Code

1exec dbms_flashback.enable_at_time(to_date('2006-AUG-24 12:00:00', 'YYYY-MON-DD HH24:MI:SS'));

Breakdown of the Code

Full Statement

1exec dbms_flashback.enable_at_time(
2    to_date('2006-AUG-24 12:00:00', 'YYYY-MON-DD HH24:MI:SS')
3);

Part-by-Part Explanation

PartWhat It Means
execSQL*Plus shortcut for EXECUTE — runs a single PL/SQL statement
dbms_flashbackBuilt-in Oracle package for session-level flashback operations
.enable_at_time(...)Procedure that turns on flashback mode for the whole session, anchored to a specific time
to_date(...)Converts a text string into an Oracle DATE value
'2006-AUG-24 12:00:00'The target past date and time you want to flash back to
'YYYY-MON-DD HH24:MI:SS'The format mask that tells Oracle how to read the date string

Once this runs, Oracle internally maps the time you gave to the closest System Change Number (SCN) in the undo tablespace. Every query in your session then reads data consistent with that SCN.

Prerequisites

Before running this code, two conditions must be true.

1. undo_management Must Be AUTO

Automatic Undo Management (AUM) must be enabled. This means Oracle manages undo (rollback) data automatically in a dedicated undo tablespace. You can verify this with:

1show parameter undo_management;

The value must return AUTO. If it returns MANUAL, flashback queries using DBMS_FLASHBACK will not work.

2. Check undo_retention First

The undo_retention parameter (in seconds) tells Oracle the minimum time to keep committed undo data before it can be overwritten. Run this to check your setting:

1show parameter undo_retention;

The default is 900 seconds (15 minutes). If you want to flash back hours or days, this value must be large enough — or you will get a "Snapshot Too Old" error. To retain undo for 24 hours, set it to 86400:

1alter system set undo_retention = 86400;

**Note:** If your undo tablespace uses AUTOEXTEND, Oracle automatically tunes undo retention to just exceed the longest-running query. This may still be too short for flashback. You may need to set undo_retention manually or use a fixed-size undo tablespace.

Key Restriction: Cannot Run as SYS

You cannot run DBMS_FLASHBACK procedures as the SYS user. Oracle will return:

1ORA-08185: Flashback not supported for user SYS

Use SYSTEM or any other DBA-privileged user instead. The SYS user can still use AS OF clause flashback queries, but not the DBMS_FLASHBACK package.

How to Use It: A Full Working Example

 1-- Step 1: Connect as SYSTEM (not SYS)
 2-- Step 2: Enable flashback to a past time
 3exec dbms_flashback.enable_at_time(
 4    to_date('2006-AUG-24 12:00:00', 'YYYY-MON-DD HH24:MI:SS')
 5);
 6
 7-- Step 3: Run your historical queries
 8SELECT * FROM my_table WHERE id = 1001;
 9
10-- Step 4: Always disable flashback when done
11exec dbms_flashback.disable;

Always call DBMS_FLASHBACK.DISABLE after your session work is complete. While in flashback mode, you cannot perform DML (INSERT, UPDATE, DELETE).

Key Insights

  • SCN mapping: Oracle does not store data by clock time. Internally, it maps your to_date value to the nearest SCN. If undo for that SCN no longer exists, you get a "Snapshot Too Old" error.
  • Session scope only: Flashback mode applies only to your current session. Other users and sessions see current data normally.
  • Compare past vs. present: A powerful pattern is to enable flashback, store past results in a temp table, disable flashback, then join the temp table with current data to see what changed.
  • No DML in flashback mode: While DBMS_FLASHBACK is enabled, your session is read-only. You must call DBMS_FLASHBACK.DISABLE before writing data.
  • Automatic undo tuning risk: In databases with AUTOEXTEND undo tablespaces, Oracle tunes retention for active queries, not for flashback. For reliable flashback over hours or days, explicitly set undo_retention.
  • Alternative — AS OF clause: For simpler one-off queries, you can use SELECT \* FROM table AS OF TIMESTAMP ... without needing the DBMS_FLASHBACK package, and this even works for the SYS user.

Quick Reference: DBMS_FLASHBACK Procedures

ProcedurePurpose
ENABLE_AT_TIME(timestamp)Flash session back to a specific wall-clock time
ENABLE_AT_SYSTEM_CHANGE_NUMBER(scn)Flash session back to a specific SCN
GET_SYSTEM_CHANGE_NUMBERReturns the current SCN — useful for saving a snapshot point
DISABLEEnds flashback mode and returns session to current data

References

Posts in this series