How to Restore Dropped Tables from the Recycle Bin in Oracle

A table dropped by mistake is not necessarily gone. By default, DROP TABLE moves the table to the recycle bin, renamed, with its indexes, constraints, and data. FLASHBACK TABLE ... TO BEFORE DROP brings it back, and FLASHBACK TABLE ... TO SCN or TO TIMESTAMP rewinds the rows of an existing table after a wrong DELETE or UPDATE.

Code for This Guide

The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

drop table [if exists] table [cascade constraints] [purge]
flashback table table to before drop [rename to new_name]
purge {table table | recyclebin}

flashback table table to {scn | timestamp} expr

Drop and Restore

The example copies a FARE_RULES table, created first:

Example:

create table fare_rules (
  rule_id       number        constraint fare_rules_pk primary key,
  route_id      number        not null constraint fare_rules_route_fk references routes,
  cabin         varchar2(8)   default 'ECONOMY' not null,
  valid_from    date          not null,
  valid_to      date,
  base_fare     number(8,2)   not null,
  refundable    boolean       default false,
  notes         varchar2(200),
  constraint fare_rules_cabin_ck check (cabin in ('BUSINESS', 'ECONOMY')),
  constraint fare_rules_dates_ck check (valid_to is null or valid_to > valid_from),
  constraint fare_rules_uk unique (route_id, cabin, valid_from)
);

insert into fare_rules (rule_id, route_id, valid_from, base_fare)
values (1, 1, date '2026-04-01', 520);

select rule_id, route_id, cabin, valid_from, base_fare, refundable from fare_rules;

Output:

Table FARE_RULES created.

1 row inserted.

   RULE_ID    ROUTE_ID CABIN      VALID_FROM        BASE_FARE REFUNDABLE
__________ ___________ __________ ______________ ____________ _____________
         1           1 ECONOMY    01-APR-2026             520 false

The copy is dropped, found in USER_RECYCLEBIN, restored, and finally dropped with PURGE, which bypasses the recycle bin.

Example:

create table old_fares as select * from fare_rules;
drop table old_fares;

select original_name, operation, type from user_recyclebin;

flashback table old_fares to before drop;
select count(*) as restored_rows from old_fares;

drop table old_fares purge;
select count(*) as in_recyclebin from user_recyclebin where original_name = 'OLD_FARES';

Output:

Table OLD_FARES created.

Table OLD_FARES dropped.

ORIGINAL_NAME    OPERATION    TYPE
________________ ____________ ________
OLD_FARES        DROP         TABLE

Flashback succeeded.

   RESTORED_ROWS
________________
               1

Table OLD_FARES dropped.

   IN_RECYCLEBIN
________________
               0

Restore under Another Name

If a new table has taken the name in the meantime, restore the old one under a different name.

Example:

create table temp_fares as select route_id, distance_km from routes where route_id <= 5;
drop table temp_fares;
create table temp_fares (route_id number);       -- the name is now taken by a new table

-- restore the dropped table under another name
flashback table temp_fares to before drop rename to temp_fares_restored;
select count(*) as restored_rows from temp_fares_restored;

Output:

Table TEMP_FARES created.

Table TEMP_FARES dropped.

Table TEMP_FARES created.

Flashback succeeded.

   RESTORED_ROWS
________________
               5

Drop the work tables afterward with drop table temp_fares purge and drop table temp_fares_restored purge.

Rewind a Table's Rows

FLASHBACK TABLE ... TO SCN restores the rows of an existing table from undo. The table needs ENABLE ROW MOVEMENT, and the example was prepared like this:

Setup:

create table fare_history as
select route_id, distance_km, 100 as fare from routes where route_id <= 3;
alter table fare_history enable row movement;
-- wait a few seconds before the flashback

The block records the SCN, deletes all rows by mistake, commits, and flashes the table back. DDL takes no bind variables, so the statement is built with the SCN in it.

Example:

declare
  v_scn number := dbms_flashback.get_system_change_number;   -- before the mistake
begin
  delete from fare_history;
  commit;
  -- DDL takes no bind variables: build the statement with the SCN in it
  execute immediate 'flashback table fare_history to scn ' || v_scn;
end;
/
select count(*) as rows_back from fare_history;

Output:

PL/SQL procedure successfully completed.

   ROWS_BACK
____________
           3

Things to Know

• The recycle bin keeps dropped tables until space is needed or they are purged; do not rely on it as a backup.
• TRUNCATE does not use the recycle bin and cannot be flashed back with TO BEFORE DROP.
• FLASHBACK TABLE TO SCN cannot reach back past a change to the table's definition.

Related Guides

• How to Query Past Data with Flashback Query in Oracle
• How to Use IF NOT EXISTS in Oracle DDL

Conclusion

DROP TABLE moves a table to the recycle bin, and FLASHBACK TABLE ... TO BEFORE DROP restores it, under its old or a new name, while PURGE removes it for good. FLASHBACK TABLE ... TO SCN or TO TIMESTAMP rewinds the rows of a table with row movement enabled.