In Oracle Database operations, one of the most heart-stopping moments is realizing you have accidentally executed a DROP TABLE on critical data. Fortunately, Oracle provides a powerful rescue feature called Flashback Drop.
Much like the “Recycle Bin” in Windows, this feature temporarily stores deleted tables and allows you to restore them instantly. This guide explains how it works and how to use it.
🔰 What is Flashback Drop?
Flashback Drop is a feature that restores a dropped table from the Recycle Bin to its original state.
When you drop a table, the data on the disk is not immediately erased. Instead, Oracle internally renames the object to a system-generated name starting with BIN$ and keeps it in a temporary storage state.
✅ Prerequisites and Configuration
To use this feature, the Recycle Bin must be enabled beforehand.
| Item | Command / Check Method | Remarks |
| Enable RECYCLEBIN | SHOW PARAMETER recyclebin; | The VALUE must be ON. |
| Tablespace Space | Check DBA_FREE_SPACE, etc. | Objects are automatically purged if space runs low. |
| Object Types | — | Indexes are saved along with the table, but check constraints carefully. |
Pro Tip: Unlike other Flashback features (Query or Table), this does not require UNDO information. Since it uses the actual data preserved in the Recycle Bin, you don’t need to worry about the UNDO retention period.
🛠 Practice: Flashback Drop Procedures
Let’s walk through the commands to create, drop, and then recover a table.
1. Prepare Verification Data
-- Create table
CREATE TABLE test_flashback (
id NUMBER,
name VARCHAR2(100)
);
-- Insert data
INSERT INTO test_flashback VALUES (1, 'Oracle');
COMMIT;
2. Execute the Accidental Operation (DROP)
The table is accidentally deleted.
DROP TABLE test_flashback;
3. Check the Recycle Bin
Verify where the deleted table is located.
SHOW RECYCLEBIN;
-- ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-- ---------------- ------------------------------ ------------ ---------------------
-- TEST_FLASHBACK BIN$KJ32lskjf23jlf== TABLE 2026-05-07:22:00:00
4. Execute Flashback Drop (Restoration)
Use the “magic command” to return the table to its state before the drop.
FLASHBACK TABLE test_flashback TO BEFORE DROP;
5. Verify Restoration
SELECT * FROM test_flashback;
-- Success if the data from before the drop is displayed correctly!
🚨 Caution! Cases of “Permanent Deletion”
Flashback Drop cannot be used in the following scenarios because the object does not enter the Recycle Bin:
- Drop with PURGE Option: If you execute
DROP TABLE xxx PURGE;. - Manual Purge: If you cleared it from the bin using
PURGE TABLE xxx;. - Dropped User: If the table was removed as part of
DROP USER xxx CASCADE;. - Tablespace Pressure: If disk space is low, Oracle automatically empties the oldest items in the Recycle Bin.
- SYSTEM Tablespace: Objects located in the
SYSTEMtablespace are not supported.
🧹 Managing and Configuring the Recycle Bin
Clearing Unnecessary Data
-- Permanently delete a specific table only
PURGE TABLE test_flashback;
-- Empty your own Recycle Bin
PURGE RECYCLEBIN;
-- Empty the entire database Recycle Bin (Requires SYSDBA privileges)
PURGE DBA_RECYCLEBIN;
Toggling the Feature ON/OFF
-- Disable temporarily for the current session
ALTER SESSION SET recyclebin = OFF;
-- Disable for the entire system (Takes effect after restart)
ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;
FAQ
Q: When I restore a table, do the index names also revert to original?
A: The data is restored, but indexes and constraints may keep their system-generated names (e.g., BIN$...). You can rename them if necessary using ALTER INDEX "BIN$..." RENAME TO my_idx;.
Q: I dropped two different tables with the same name. What happens?
A: Both remain in the Recycle Bin. If you specify only the table name, the most recently dropped version is restored. To restore a specific one, specify the RECYCLEBIN NAME (BIN$...) directly in the Flashback command.
Q: Is this available in Standard Edition?
A: Yes. Flashback Drop is a standard, user-friendly feature available in Standard Edition.
Summary
| Point | Content |
| Core Function | Rescues dropped tables from the “Recycle Bin.” |
| Primary Command | FLASHBACK TABLE [table_name] TO BEFORE DROP; |
| Prerequisite | The RECYCLEBIN parameter must be ON. |
| Operational Tip | Use PURGE periodically to keep the bin organized and save space. |
Flashback Drop is a powerful safety net for accidental mistakes, but it is not infallible—availability depends on disk space and whether the PURGE option was used. Always maintain regular backup operations rather than relying solely on this feature.
If you need to revert only a few rows to a state from 10 minutes ago (rather than restoring a whole dropped table), consider using Flashback Query.
Verification Environment: Oracle Database 19c. It is strongly recommended to take a full backup before executing in a production environment.
[reference]
FLASHBACK TABLE
コメント