Oracle Recycle Bin: The Complete DBA Guide

Everything you need to know — from concepts to day-to-day commands

What Is the Oracle Recycle Bin?

Introduced in Oracle 10g, the Recycle Bin (also called Flashback Drop) is a logical container within each tablespace where Oracle stores dropped objects instead of immediately deallocating their storage. Think of it like the Windows Recycle Bin — objects land there first and can be recovered unless you explicitly purge them or Oracle reclaims the space automatically.

When you drop a table without the PURGE clause, Oracle renames it with a system-generated BIN$ name (e.g., BIN$u4qspB/IRC+gQKjAZYoFaw==$0), keeps it in the same tablespace, and moves all dependent objects (indexes, triggers, constraints, LOB segments) alongside it. The original name is preserved in metadata so recovery is straightforward.

Key Concepts

Space Management: The Recycle Bin has no fixed size — it occupies space within the object’s original tablespace. When a tablespace runs low, Oracle auto-purges the oldest Recycle Bin objects first (FIFO).

Scope: Every user has their own Recycle Bin. DBAs can see all users’ objects via DBA_RECYCLEBIN.

What bypasses the Recycle Bin entirely (permanent drop):

• Objects in the SYSTEM tablespace or owned by SYS
• Partitioned tables (prior to Oracle 11g)
• Materialized views
• Objects dropped with DROP ... PURGE
• Tablespaces created with RECYCLEBIN OFF

Enable / Disable

-- Check current status
SHOW PARAMETER recyclebin;
SELECT name, value FROM v$parameter WHERE name = 'recyclebin';

-- Enable (system-wide, persistent)
ALTER SYSTEM SET recyclebin = ON SCOPE=BOTH;

-- Enable (session only)
ALTER SESSION SET recyclebin = ON;

-- Disable (system-wide, persistent)
-- Note: existing Recycle Bin contents are NOT purged on disable
ALTER SYSTEM SET recyclebin = OFF SCOPE=BOTH;

-- Disable (session only)
ALTER SESSION SET recyclebin = OFF;

Viewing Recycle Bin Contents

Your own Recycle Bin

-- Quick look
SELECT * FROM RECYCLEBIN;

-- Detailed, formatted view
SELECT  object_name,      -- BIN$... system name
        original_name,    -- Name before the drop
        type,             -- TABLE, INDEX, TRIGGER, etc.
        ts_name,          -- Tablespace
        droptime,         -- When it was dropped
        can_undrop,       -- YES = recoverable
        can_purge,        -- YES = can be purged
        space             -- Space in blocks
FROM    USER_RECYCLEBIN
ORDER BY droptime DESC;

All users’ Recycle Bins (DBA)

SELECT  owner,
        object_name,
        original_name,
        type,
        ts_name,
        droptime,
        can_undrop,
        ROUND(space * 8192 / 1048576, 2) AS size_mb
FROM    DBA_RECYCLEBIN
ORDER BY owner, droptime DESC;

Find a specific table

-- As a regular user
SELECT object_name, original_name, droptime, can_undrop
FROM   USER_RECYCLEBIN
WHERE  original_name = 'EMPLOYEES'
AND    type = 'TABLE'
ORDER BY droptime DESC;

-- As DBA (search across all owners)
SELECT owner, object_name, original_name, droptime, can_undrop
FROM   DBA_RECYCLEBIN
WHERE  owner = 'HR'
AND    original_name = 'EMPLOYEES'
ORDER BY droptime DESC;

Tip: If the same table was dropped multiple times, multiple BIN$ entries will appear. Order by droptime DESC — the most recent drop is listed first. Use the specific BIN$ name to target the exact version you want.

Space usage reports

-- Space consumed per tablespace
SELECT  ts_name,
        COUNT(*)                                    AS object_count,
        ROUND(SUM(space) * 8192 / 1048576, 2)      AS size_mb
FROM    DBA_RECYCLEBIN
GROUP BY ts_name
ORDER BY size_mb DESC;

-- Space consumed per user
SELECT  owner,
        COUNT(*)                                    AS objects,
        ROUND(SUM(space) * 8192 / 1048576, 2)      AS size_mb
FROM    DBA_RECYCLEBIN
GROUP BY owner
ORDER BY size_mb DESC;

Recovering (Undropping) Objects

-- Recover by original table name (gets the most recently dropped version)
FLASHBACK TABLE employees TO BEFORE DROP;

-- Recover and rename in one step (use when original name already exists)
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_recovered;

-- Recover a specific version using the BIN$ name (double quotes required)
FLASHBACK TABLE "BIN$u4qspB/IRC+gQKjAZYoFaw==$0" TO BEFORE DROP;

-- As DBA, recover a table in another schema
FLASHBACK TABLE hr.employees TO BEFORE DROP;

After recovery — fix index names

Indexes are recovered with their BIN$ names and must be renamed manually:

-- Find indexes that still have BIN$ names on the recovered table
SELECT index_name
FROM   user_indexes
WHERE  table_name = 'EMPLOYEES'
AND    index_name LIKE 'BIN$%';

-- Rename them back
ALTER INDEX "BIN$u4qspB/IRC+gQKjAZYoFaw==$0" RENAME TO emp_pk;

Check recoverability before attempting flashback

SELECT original_name, can_undrop, can_purge, droptime
FROM   USER_RECYCLEBIN
WHERE  original_name = 'EMPLOYEES';

can_undrop = NO means a new object with the same name already exists, or the tablespace has no room. Rename or drop the conflicting object first, or use RENAME TO during the flashback.

Querying a Dropped Table Without Recovering It

You can SELECT directly from a BIN$ object to inspect data before deciding whether to recover:

SELECT * FROM "BIN$u4qspB/IRC+gQKjAZYoFaw==$0";

SELECT COUNT(*) FROM "BIN$u4qspB/IRC+gQKjAZYoFaw==$0";

Purging the Recycle Bin

Oracle’s PURGE TABLE and PURGE INDEX support both the original object name and the BIN$ system-generated name, with or without a schema prefix. All of the forms below are valid.

-- Purge by original table name (removes the OLDEST version if multiple exist)
PURGE TABLE employees;

-- Purge by original name, schema-qualified (DBA purging another user's object)
PURGE TABLE hr.employees;

-- Purge a specific version by BIN$ name (no schema needed — BIN$ names are globally unique)
PURGE TABLE "BIN$u4qspB/IRC+gQKjAZYoFaw==$0";

-- Purge a specific version by BIN$ name with owner prefix (also valid, common in DBA scripts)
PURGE TABLE hr."BIN$u4qspB/IRC+gQKjAZYoFaw==$0";

-- Purge a specific index by original name
PURGE INDEX emp_pk;

-- Purge a specific index by original name, schema-qualified
PURGE INDEX hr.emp_pk;

-- Purge a specific index by BIN$ name
PURGE INDEX "BIN$u4qspB/IRC+gQKjAZYoFaw==$0";

-- Purge your own entire Recycle Bin
PURGE RECYCLEBIN;

-- Purge all objects in a specific tablespace (DBA)
PURGE TABLESPACE users;

-- Purge one specific user's objects in a tablespace (DBA)
PURGE TABLESPACE users USER hr;

-- Purge ALL users' Recycle Bins (DBA / SYSDBA only — use with care)
PURGE DBA_RECYCLEBIN;

-- Drop a table permanently, bypassing the Recycle Bin entirely
DROP TABLE employees PURGE;

Note on PURGE TABLE by original name: When multiple dropped versions of the same table exist, PURGE TABLE tablename always removes the oldest version first. To target a specific version, use its unique BIN$ name.

Monitoring & Reporting Scripts

Daily Recycle Bin report (DBA)

SELECT  owner,
        original_name,
        type,
        ts_name,
        TO_CHAR(TO_DATE(droptime,'YYYY-MM-DD:HH24:MI:SS'),'DD-MON-YYYY HH24:MI') AS drop_time,
        ROUND(space * 8192 / 1048576, 2)  AS size_mb,
        can_undrop
FROM    DBA_RECYCLEBIN
WHERE   TO_DATE(droptime,'YYYY-MM-DD:HH24:MI:SS') >= SYSDATE - 1
ORDER BY drop_time DESC;

Find large objects consuming Recycle Bin space

SELECT  owner,
        original_name,
        type,
        ts_name,
        ROUND(space * 8192 / 1048576, 2) AS size_mb
FROM    DBA_RECYCLEBIN
WHERE   space * 8192 / 1048576 > 100   -- objects larger than 100 MB
ORDER BY space DESC;

Tablespace pressure check — how much space can the Recycle Bin release?

SELECT  rb.ts_name,
        ROUND(SUM(rb.space) * 8192 / 1048576, 2)                       AS recbin_mb,
        ROUND(fs.free_mb, 2)                                            AS current_free_mb,
        ROUND(fs.free_mb + SUM(rb.space) * 8192 / 1048576, 2)         AS potential_free_mb
FROM    DBA_RECYCLEBIN rb
JOIN    (
            SELECT tablespace_name, SUM(bytes) / 1048576 AS free_mb
            FROM   dba_free_space
            GROUP BY tablespace_name
        ) fs ON rb.ts_name = fs.tablespace_name
GROUP BY rb.ts_name, fs.free_mb
ORDER BY recbin_mb DESC;

Tablespaces over 85% full that could benefit from a purge

SELECT  t.tablespace_name,
        ROUND((1 - f.free_bytes / t.total_bytes) * 100, 2) AS pct_used,
        ROUND(f.free_bytes / 1048576, 2)                   AS free_mb
FROM    (SELECT tablespace_name, SUM(bytes) AS total_bytes FROM dba_data_files GROUP BY tablespace_name) t
JOIN    (SELECT tablespace_name, SUM(bytes) AS free_bytes  FROM dba_free_space  GROUP BY tablespace_name) f
        ON t.tablespace_name = f.tablespace_name
WHERE   (1 - f.free_bytes / t.total_bytes) * 100 > 85
ORDER BY pct_used DESC;

-- Then selectively purge the offending tablespace
PURGE TABLESPACE <tablespace_name>;

-- Or target just one user in that tablespace
PURGE TABLESPACE <tablespace_name> USER <username>;

Generate PURGE statements for all objects in a tablespace (DBA script)

Useful when you want to review before purging, or purge selectively by owner:

SELECT DISTINCT
    'PURGE TABLE ' || owner || '."' || object_name || '";' AS purge_stmt
FROM DBA_RECYCLEBIN
WHERE ts_name = 'USERS'
AND   type    = 'TABLE'
ORDER BY 1;

Common DBA Scenarios

Scenario 1: Developer accidentally dropped a table

-- Step 1: Confirm it's recoverable
SELECT owner, original_name, droptime, can_undrop
FROM   DBA_RECYCLEBIN
WHERE  owner = 'HR' AND original_name = 'ORDERS';

-- Step 2: Recover it
FLASHBACK TABLE hr.orders TO BEFORE DROP;

-- Step 3: Verify
SELECT COUNT(*) FROM hr.orders;

Scenario 2: Table dropped multiple times — recover the right version

-- List all versions with drop times and BIN$ names
SELECT object_name, original_name, droptime
FROM   DBA_RECYCLEBIN
WHERE  owner = 'SCOTT' AND original_name = 'ORDERS'
ORDER BY droptime DESC;

-- Flashback the exact version using its unique BIN$ name
FLASHBACK TABLE "BIN$u4qspB/IRC+gQKjAZYoFaw==$0" TO BEFORE DROP;

Scenario 3: Tablespace near full — reclaim Recycle Bin space fast

-- Step 1: Check how much the Recycle Bin holds for that tablespace
SELECT ts_name, ROUND(SUM(space) * 8192 / 1048576, 2) AS recbin_mb
FROM   DBA_RECYCLEBIN
WHERE  ts_name = 'USERS'
GROUP BY ts_name;

-- Step 2: Purge it
PURGE TABLESPACE users;

-- Step 3: Confirm space is back
SELECT tablespace_name, SUM(bytes) / 1048576 AS free_mb
FROM   dba_free_space
WHERE  tablespace_name = 'USERS'
GROUP BY tablespace_name;

Scenario 4: Purge one user’s objects without touching others

-- Only removes HR's Recycle Bin objects from the USERS tablespace
PURGE TABLESPACE users USER hr;

Scenario 5: ETL / maintenance script — skip the Recycle Bin

-- For staging tables that are dropped and recreated routinely
DROP TABLE stg_orders PURGE;

Views Reference

Quick Command Cheat Sheet

-- ── STATUS ────────────────────────────────────────────────────────────
SHOW PARAMETER recyclebin;
SELECT name, value FROM v$parameter WHERE name = 'recyclebin';

-- ── VIEW ──────────────────────────────────────────────────────────────
SELECT * FROM RECYCLEBIN;                              -- My Recycle Bin
SELECT * FROM DBA_RECYCLEBIN;                          -- All users (DBA)

-- ── RECOVER ───────────────────────────────────────────────────────────
FLASHBACK TABLE employees TO BEFORE DROP;
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_recovered;
FLASHBACK TABLE "BIN$u4qspB/IRC+gQKjAZYoFaw==$0" TO BEFORE DROP;
FLASHBACK TABLE hr.employees TO BEFORE DROP;           -- Another schema (DBA)

-- ── QUERY WITHOUT RECOVERING ──────────────────────────────────────────
SELECT * FROM "BIN$u4qspB/IRC+gQKjAZYoFaw==$0";

-- ── PURGE (selective) ─────────────────────────────────────────────────
PURGE TABLE employees;                                 -- Oldest version by name
PURGE TABLE hr.employees;                              -- Schema-qualified name
PURGE TABLE "BIN$u4qspB/IRC+gQKjAZYoFaw==$0";        -- Specific version by BIN$
PURGE TABLE hr."BIN$u4qspB/IRC+gQKjAZYoFaw==$0";     -- Schema + BIN$ (DBA scripts)
PURGE INDEX emp_pk;
PURGE INDEX hr.emp_pk;
PURGE INDEX "BIN$u4qspB/IRC+gQKjAZYoFaw==$0";
PURGE TABLESPACE users;                                -- Whole tablespace (DBA)
PURGE TABLESPACE users USER hr;                        -- One user's objects (DBA)

-- ── PURGE (bulk) ──────────────────────────────────────────────────────
PURGE RECYCLEBIN;                                      -- My Recycle Bin
PURGE DBA_RECYCLEBIN;                                  -- Everyone's (SYSDBA only)

-- ── BYPASS RECYCLE BIN ────────────────────────────────────────────────
DROP TABLE employees PURGE;

-- ── ENABLE / DISABLE ──────────────────────────────────────────────────
ALTER SYSTEM SET recyclebin = ON  SCOPE=BOTH;
ALTER SYSTEM SET recyclebin = OFF SCOPE=BOTH;
ALTER SESSION SET recyclebin = ON;
ALTER SESSION SET recyclebin = OFF;

Best Practices

• Keep it ON in production. The overhead is negligible; the recovery capability is invaluable. One accidental DROP TABLE recovered without downtime justifies it entirely.
• Use DROP ... PURGE in ETL and scripts. Staging tables dropped and recreated regularly should bypass the Recycle Bin to avoid accumulating dead objects and consuming tablespace silently.
• Include Recycle Bin size in tablespace monitoring. A large Recycle Bin won’t trigger space alerts immediately (Oracle auto-evicts when needed), but it masks your true available capacity.
• Use owner."BIN$..." in DBA scripts for precision. When purging from another user’s Recycle Bin, qualifying with the owner name alongside the BIN$ system name is the cleanest, most explicit approach and avoids any ambiguity.
• After FLASHBACK TABLE, always check index names. Recovered indexes retain their BIN$ names. Rename them with ALTER INDEX "BIN$..." RENAME TO original_name.
• When multiple versions exist, use BIN$ names — not the original name. PURGE TABLE tablename always targets the oldest version. FLASHBACK TABLE tablename always targets the newest. If you need a specific middle version, always reference it by its unique BIN$ name.
• Disable only with a clear justification. System-level recyclebin = OFF makes every DROP permanent. Prefer session-level disable for specific maintenance scripts where you’ve already validated the operation.