Oracle: How to Disable Flashback Using DBMS_FLASHBACK.DISABLE

calendar Mar 11, 2026 / · 5 min read · Oracle Flashback PL/SQL DBA Database Administration Oracle Scripts Backup Recovery Admin Guide  ·
Share on: linkedin copy

Overview

When working with Oracle Database, there are times when you need to look at data from the past — like rewinding a video to see what happened earlier. Oracle gives you a tool called Flashback to do this. But once you are done looking at past data, you must turn Flashback off. The command to do that is:

1EXEC DBMS_FLASHBACK.DISABLE;

This post explains what this command does, how it works, and when to use it.

What Is Oracle Flashback?

Oracle Flashback Technology is a set of features inside Oracle Database that lets you view or recover past states of data without restoring a full backup.

You can use Oracle Flashback to:

  • Query data as it looked at a past point in time
  • See the history of changes made to database objects
  • Recover tables or rows to a previous state
  • Roll back a transaction while the database stays online

The DBMS_FLASHBACK package is the PL/SQL interface that controls session-level Flashback. It lets your current database session "travel back in time" to a specific System Change Number (SCN) or timestamp.

The Code

1EXEC DBMS_FLASHBACK.DISABLE;

Code Breakdown

PartDescription
EXECShort for EXECUTE — runs a PL/SQL procedure from SQL*Plus or SQLcl
DBMS_FLASHBACKThe Oracle built-in package that manages session-level Flashback
.DISABLEThe procedure inside the package that turns Flashback mode off

Full Syntax (PL/SQL block form)

1BEGIN
2  DBMS_FLASHBACK.DISABLE;
3END;
4/

Both forms do the same thing. The EXEC shortcut is more common for quick, single-line execution in SQL*Plus.

Purpose

DBMS_FLASHBACK.DISABLE turns off Flashback mode for the current database session.

When Flashback mode is active on a session, every query that session runs returns data as it existed at the past SCN or timestamp you set — not the current data. Once you call DISABLE, the session goes back to reading live, current data.

This is important because:

  • You cannot run DML (INSERT, UPDATE, DELETE) while Flashback mode is active on a session
  • You must call DISABLE before calling ENABLE again with a new time or SCN
  • Flashback mode ends automatically when the session disconnects, but calling DISABLE explicitly is a best practice

A Real-World Example

Here is a common use case — check the salary of an employee as it was on a specific past date, then return to the present:

1-- Step 1: Enable Flashback to a past point in time
2EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME('30-AUG-2000');
3
4-- Step 2: Query past data
5SELECT salary FROM emp WHERE name = 'Joe';
6
7-- Step 3: Disable Flashback — return to present data
8EXECUTE DBMS_FLASHBACK.DISABLE;

After Step 3, all queries on the session will again return current, live data.

Key Points

  • Session-scoped only — DBMS_FLASHBACK.DISABLE affects only the current session, not the entire database. Other sessions are not impacted.
  • No parameters needed — The DISABLE procedure takes zero arguments. It is a simple on/off switch.
  • Required before re-enabling — You must call DISABLE before you can call ENABLE_AT_TIME or ENABLE_AT_SYSTEM_CHANGE_NUMBER again in the same session.
  • No DML in Flashback mode — While Flashback is enabled on a session, you cannot perform writes (INSERT, UPDATE, DELETE). Call DISABLE first, then do your DML.
  • Automatic disable on session end — Oracle automatically disables Flashback when your session ends or disconnects, but relying on this is not considered good practice.
  • Requires EXECUTE privilege — The user running this must have the EXECUTE privilege granted on the DBMS_FLASHBACK package.

Insights and Tips

Tip 1 — Always pair ENABLE with DISABLE Think of Flashback like opening a file. If you open it, always close it. Pair every ENABLE_AT_TIME or ENABLE_AT_SYSTEM_CHANGE_NUMBER call with a matching DISABLE call in your PL/SQL block or script.

Tip 2 — Use inside PL/SQL for safety When writing stored procedures or scripts, wrap ENABLE and DISABLE inside a BEGIN...EXCEPTION...END block. This makes sure DISABLE is always called, even if an error happens.

 1BEGIN
 2  DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - 1/24); -- 1 hour ago
 3  -- your read-only queries here
 4  DBMS_FLASHBACK.DISABLE;
 5EXCEPTION
 6  WHEN OTHERS THEN
 7    DBMS_FLASHBACK.DISABLE; -- always clean up
 8    RAISE;
 9END;
10/

Tip 3 — DBMS_FLASHBACK vs. AS OF DBMS_FLASHBACK enables Flashback for the whole session. For a single query, using the AS OF clause is simpler and does not require calling DISABLE:

1SELECT salary FROM emp AS OF TIMESTAMP (SYSDATE - INTERVAL '1' HOUR)
2WHERE name = 'Joe';

Use DBMS_FLASHBACK when you need multiple queries across the same past snapshot.

Tip 4 — This is NOT the same as disabling Flashback Database EXEC DBMS_FLASHBACK.DISABLE turns off session-level flashback query. It is different from disabling the Flashback Database feature (which uses flashback logs and is set at the database level):

1-- This is database-level flashback, not session-level
2ALTER DATABASE FLASHBACK OFF;

Do not confuse the two.

Required Privilege

To use DBMS_FLASHBACK, the Oracle DBA must grant execute permission to the user:

1GRANT EXECUTE ON DBMS_FLASHBACK TO your_user;

Without this grant, the user will get an ORA-06550 or PLS-00201 error when trying to run the procedure.

References

Posts in this series