Oracle DROP TABLE and Flashback Drop: How to Restore Dropped Tables

In database management, “accidentally deleting an important table with DROP TABLE” is a moment that causes a cold sweat. However, Oracle Database is equipped with a Flashback Drop feature that temporarily holds deleted tables and allows for easy restoration, much like the “Recycle Bin” on Windows or Mac.

This article explains everything from basic deletion commands to the mechanism of the Recycle Bin, and recovery procedures in case of an emergency, using SQL examples running on an actual machine. The content targets Oracle Database 19c.

[Conclusion] Quick Command List for Drop and Restore

For those in a hurry, here is a summary of frequently used commands.

Move table to the recycle bin (Normal deletion)

DROP TABLE table_name;

Restore a deleted table (Flashback Drop)

FLASHBACK TABLE table_name TO BEFORE DROP;

Delete completely without using the recycle bin (Cannot be restored)

DROP TABLE table_name PURGE;

Check the contents of the recycle bin

SELECT * FROM user_recyclebin;
-- Or the SQL*Plus command: SHOW RECYCLEBIN;

1. What is Flashback Drop?

Flashback Drop is a feature that returns a table deleted by a DROP TABLE statement to its original state from the Oracle “Recycle Bin”.

Mechanism and Features

Logical Deletion: Even if you execute a normal DROP TABLE, the space on the data file is not immediately released. Simply put, the “table name” is rewritten in the data dictionary, and it is treated as having been moved to the “Recycle Bin” for management purposes.
Dependent Objects Protected: Indexes and constraints (excluding foreign key constraints) attached to the table are also moved to the recycle bin together and are returned upon restoration.
Enabled by Default: It is usually enabled by default in both Standard Edition 2 (SE2) and Enterprise Edition (EE).

2. [Practice] Drop and Restore Steps

We will verify the flow step-by-step by actually creating a test table, deleting it, and then restoring it.

Prerequisites

OS: Oracle Linux or Windows
DB: Oracle Database 19c (CDB/PDB or Non-CDB)
User: General user (with RESOURCE privileges, etc.)
Note: Tables created by the SYS user (SYSDBA) are not subject to the recycle bin feature. Please be sure to perform this as a general user.

Step 1: Create Test Data

First, create the employees_demo table for the experiment and populate it with data.

-- Create table
CREATE TABLE employees_demo (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    dept_no NUMBER
);

-- Insert data
INSERT INTO employees_demo VALUES (101, 'Alice', 10);
INSERT INTO employees_demo VALUES (102, 'Bob', 20);
COMMIT;

-- Check data
SELECT * FROM employees_demo;

Step 2: Drop Table (DROP TABLE)

Execute the DROP TABLE statement to delete the table. Unless the PURGE option is added, the table moves to the recycle bin.

DROP TABLE employees_demo;

Execution Result: Table dropped.

At this point, if you execute SELECT * FROM employees_demo;, you will get “ORA-00942: table or view does not exist”.

SQL> SELECT * FROM employees_demo;

    EMP_ID EMP_NAME                DEPT_NO
---------- -------------------- ----------
       101 Alice                        10
       102 Bob                          20

SQL> DROP TABLE employees_demo;

Table dropped.

SQL> SELECT * FROM employees_demo;
SELECT * FROM employees_demo
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Step 3: Check Recycle Bin

Check if the table is really in the recycle bin. Query the user_recyclebin view or use the SQL*Plus SHOW command.

-- Display contents of the recycle bin
SELECT object_name, original_name, type, droptime FROM user_recyclebin;

Execution Result Example:

SQL> SELECT object_name, original_name, type, droptime
  2  FROM user_recyclebin;

OBJECT_NAME                              ORIGINAL_NAME        TYPE       DROPTIME
---------------------------------------- -------------------- ---------- -------------------
BIN$Rl6XAo07CnzgZaP6z6C6Kw==$0           SYS_C007537          INDEX      2025-12-20:03:00:39
BIN$Rl6XAo08CnzgZaP6z6C6Kw==$0           EMPLOYEES_DEMO       TABLE      2025-12-20:03:00:39

ORIGINAL_NAME: Original table name (EMPLOYEES_DEMO)
OBJECT_NAME: System-generated name in the recycle bin (Name starting with BIN$)

Step 4: Restore Table (FLASHBACK TABLE)

Use the Flashback Drop feature to return the table to the state immediately before deletion.

FLASHBACK TABLE employees_demo TO BEFORE DROP;

Execution Result: Flashback complete.

After restoration, search the data again to confirm it is back to normal.

SELECT * FROM employees_demo;

SQL> FLASHBACK TABLE employees_demo TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM employees_demo;

    EMP_ID EMP_NAME                DEPT_NO
---------- -------------------- ----------
       101 Alice                        10
       102 Bob                          20

SQL> SELECT object_name, original_name, type, droptime
  2  FROM user_recyclebin;

no rows selected

3. Permanent Delete (PURGE) and Management

If you want to free up disk space, or if you want to completely erase confidential information, you need to delete without using the recycle bin or perform an operation to empty the recycle bin.

Deletion with PURGE Option

Used when you do not want to leave it in the recycle bin from the beginning. This operation cannot be undone.

DROP TABLE employees_demo PURGE;

Manual Purge of Recycle Bin

Delete objects accumulated in the recycle bin to release space.

Delete only a specific table from the recycle bin

PURGE TABLE employees_demo;

Empty your own recycle bin entirely

PURGE RECYCLEBIN;

Empty the recycle bin for all users (For Administrators)

PURGE DBA_RECYCLEBIN;

4. Troubleshooting and Notes

Common Errors and Solutions

Error	Cause	Solution
ORA-38305: object not in RECYCLE BIN	Already `PURGE`d, deleted by SYS user, or automatically purged due to lack of space.	Restoration is impossible (Restore from RMAN backup is required).
ORA-00955: name is already used by an existing object	A table with the same name as the one you are trying to restore has already been re-created.	Restore using a different name using the `RENAME TO` option (See FAQ below).

Important Operational Notes

Automatic Purge During Space Shortage

When the free space in the tablespace becomes insufficient, Oracle automatically deletes (purges) objects from the old recycle bin to accommodate new data. If you leave objects thinking “it’s safe because it’s in the recycle bin,” they may disappear before you know it.

SYSTEM Tablespace Limitations

Objects created in the SYSTEM tablespace are not subject to the recycle bin feature.

SYS User

If a table owned by the SYS user (logged in with SYSDBA privileges) is deleted, it does not go into the recycle bin and is immediately and completely deleted.

5. Frequently Asked Questions (FAQ)

Q1. I created a new table with the same name, but can I restore the old deleted table?

A. Yes, it can be restored by renaming it.

If you have newly created a table named employees_demo after deletion, restoring it as is will cause a name conflict. Specify a different name and restore as follows:

FLASHBACK TABLE employees_demo TO BEFORE DROP 
RENAME TO employees_demo_old;

Q2. Will indexes and constraints also return?

A. Yes, basically they will return.

However, the names of indexes and constraints may not return to their original names and may remain as system-generated names like BIN$…. It is recommended to correct the names with ALTER INDEX … RENAME TO … after restoration if necessary.

Note: Referential integrity constraints (foreign keys) are not restored, so manual redefinition is required.

Q3. How can I check if the recycle bin feature is enabled?

A. Check the recyclebin parameter.

SHOW PARAMETER recyclebin;

If the value is on, it is enabled. If it is disabled (off), you can enable it with ALTER SYSTEM SET recyclebin = ON; (Re-connection may be required for it to take effect).

6. Summary

DROP TABLE normally only moves the table to the recycle bin, so restoration is possible with FLASHBACK TABLE.
If you want to delete completely, use the PURGE option.
Be aware that objects of the SYS user or in the SYSTEM tablespace do not enter the recycle bin.
Since old objects in the recycle bin are automatically deleted when space runs low, do not be overconfident.

Knowing recovery methods is the first step in safe database operation. First, please try the flow of CREATE → DROP → FLASHBACK in your development environment.

This article explains based on Oracle Database 19c (Screens and default values may differ for other versions).

[reference]
Oracle Database SQL Language Reference, 19c